How do I copy a range of cells between worksheets?

Question

I am getting an error with at the last line of the for loop.

Dim iLastCol As Integer
Dim teamsWsht As Worksheet

Set teamsWsht = ThisWorkbook.Worksheets("Teams")

iLastCol = teamsWsht.UsedRange.Columns.Count

For iCol = 2 To iLastCol

    Dim iLastRow As Integer
    Dim currWsht As Worksheet
    Dim teamsRng As Range
    Dim currRng As Range


    iLastRow = teamsWsht.Cells(Rows.Count, iCol).End(xlUp).Row              
    dTeams.Add iCol - 2, teamsWsht.Cells(5, iCol).Value                     
    Set currWsht = ThisWorkbook.Worksheets(dTeams(iCol - 2))                
    currWsht.Range("A1").Value = dTeams(iCol - 2)                           
  
    Set teamsRng = teamsWsht.Range(Cells(7, iCol), Cells(iLastRow, iCol))
    Set currRng = Range(Cells(2, 1), Cells(iLastRow - 7 + 1, iCol))
    
    currWsht.Range(currRng).Value = teamsRng.Value 'error here

Next iCol

I get an error 1004 method range of _worksheet failed at the last line.

I removed the set, but now I get the error Run-time error '1004': Method 'Range' of object '_Global' failed I've also noticed that I need to set the active sheet before selecting the range + you can't select a range of cells that are empty, both giving me an issue with Run-time error '1004': Method 'Range' of object '_Worksheet' failed — user13696433, Sep 07 '20 at 23:11
I am still confused, I have made the edits as per the link and receive the same error, what is missing? Set teamsRng = Range(teamsWsht.Cells(7, iCol), teamsWsht.Cells(iLastRow, iCol)) Set currRng = Range(currWsht.Cells(2, 1), currWsht.Cells(iLastRow - 7 + 1, iCol)) currWsht.Range(currRng).Value = teamsWsht.Range(teamsRng).Value with the — user13696433, Sep 08 '20 at 03:08

score 0 · Accepted Answer · answered Sep 07 '20 at 18:56

0

Remove Set at that line. Set is only used with objects.

answered Sep 07 '20 at 18:56

ENIAC

813
1
8
19

How do I copy a range of cells between worksheets?

1 Answers1