Copy/Paste Range from one sheet to another

Question

I am trying too simply copy the cells with data in col A of a worksheet to another worksheet at row 2. With the following script the source worksheet name is entered into row 1 of Ave RLD worksheet. If that is all I try to do it will loop through all the worksheets and place their names in the next col of Ave RLD. As soon as I try to copy the data from col A and paste it to Ave RLD I get a Run time error 1004. I left in all the commented lines of things I have been trying. What am I missing?

Dim WS_count As Long
Dim I As Long
Dim ws As Worksheet
Dim ColNum As Long
Dim wksName As String
Dim NumRows As Long

ColNum = 1

Sheets.Add Type:=xlWorksheet
ActiveSheet.Name = "Ave RLD"

For Each ws In ActiveWorkbook.Worksheets
    If Left(Trim(ws.Name), 3) = "RLD" Then
        wksName = ws.Name
        NumRows = ws.Range("A" & Rows.Count).End(xlUp).Row
        MsgBox NumRows
        With Worksheets("Ave RLD")
            .Cells(1, ColNum).Value = wksName
            ws.Range(Cells(1, 1), Cells(NumRows, 1)).Copy
            .Range(Cells(2, ColNum)).Paste.Values
            '.Range(Cells(2, ColNum)).Value = .Range(("A1"), Range("A1").End(xlUp))
            '.Range(Cells(2, ColNum)).Value = ws.Range("A" & Rows.Count).End(xlUp).Row
            'MsgBox ws.Cells(1, 26).Value
            '.Cells(2, ColNum).Value = .Worksheets(wksName).Cells(1, 26)
            '.Worksheets(wksName).Cells(1, 1).Copy
            'Worksheets(wksName).Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Copy
            '.Cells(2, ColNum).Paste
            
            ColNum = ColNum + 1
        End With
    End If
Next ws

SJR SJR · Accepted Answer · 2021-01-22T17:01:20

Does this do what you want?

Lots on this at this site, but this line will error if ws is not the active sheet as you do not fully qualify all the ranges

ws.Range(Cells(1, 1), Cells(NumRows, 1)).Copy

And the other line commented below just needs Range or Cells, also your paste values syntax was off - recording a macro is one way to sort out such details.

Dim WS_count As Long
Dim I As Long
Dim ws As Worksheet
Dim ColNum As Long
Dim wksName As String
Dim NumRows As Long

ColNum = 1

Sheets.Add Type:=xlWorksheet
ActiveSheet.Name = "Ave RLD"

For Each ws In ActiveWorkbook.Worksheets
    If Left(Trim(ws.Name), 3) = "RLD" Then
        wksName = ws.Name
        NumRows = ws.Range("A" & Rows.Count).End(xlUp).Row
        MsgBox NumRows
        With Worksheets("Ave RLD")
            .Cells(1, ColNum).Value = wksName
            ws.Range(ws.Cells(1, 1), ws.Cells(NumRows, 1)).Copy 'fully qualify with ws
            .Cells(2, ColNum).PasteSpecial xlpasteValues 'just Cells
            ColNum = ColNum + 1
        End With
    End If
Next ws

Copy/Paste Range from one sheet to another

1 Answers