When Creating New Workbook, Paste Select Cells into Columns

Question

This script copies all cells from C2:C3 for all sheets in the workbook. I would like for it to ignore all hidden sheets. Also the current script is pasting into the next available row when I am trying to paste into the next available column.

Option Explicit
Sub Sample()
Dim wsInput As Worksheet, wsOutput As Worksheet
Dim rng As Range
Dim LRowO As Long, LRowI As Long

'Set the output sheet
Set wsOutput = ThisWorkbook.Sheets("Master")

For Each wsInput In ThisWorkbook.Worksheets
    If wsInput.Name <> wsOutput.Name Then
        With wsInput      
            Set rng = .Range("C2:C3")
            rng.Copy
            With wsOutput             
                LRowO = .Range("A" & .Rows.Count).End(xlUp).Row + 1
                .Range("A" & LRowO).PasteSpecial xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            End With
        End With
    End If
Next wsInput

Exit Sub

End Sub

paul bica paul bica · Accepted Answer · 2015-08-12T20:18:34

Tested:

Option Explicit

Sub CommandButton()

    Dim ws1 As Worksheet, ws2 As Worksheet, wsM As Worksheet, lr As Long

    Set wsM = Worksheets("Master")
    Set ws1 = Worksheets("New_Part")
    ws1.Copy After:=Worksheets(ws1.Index)
    Set ws2 = Worksheets(ws1.Index + 1)

    lr = wsM.Cells(wsM.Rows.Count, 1).End(xlUp).Row + 1

    wsM.Range(wsM.Cells(lr, 1), wsM.Cells(lr + 1, 1)).Value2 = ws2.Range("C2:C3").Value2

End Sub

When Creating New Workbook, Paste Select Cells into Columns

1 Answers