CODEWITHSUNDEEP
excelvba
Louw
Louw

Reputation: 7

Setting up a macro to copy a range from one worksheet to another in the same workbook

I have a workbook with multiple sheets and I'm trying to setup a macro in sheet2 which will clear the contents of sheet2 and then copy from sheet6 a range of data (columns A:Q) from row 2 down to the last row of data to the corresponding columns of sheet2 from row 2. I have found some code which can do the copying but I suspect that it will only work if the source sheet (sheet6) is the active sheet and I need this to be part of a macro to be run on sheet2 as there are other things to be added later to the macro. The code I used on sheet2 is this:

Sub AImportData()


    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-3

    Dim ws As Worksheet
    Set ws = Sheets("Sheet2")

    With Sheets("Sheet6")
        .Range("A:Q").Copy Destination:=ws.Range("A2")
    End With



End Sub

This clears the data in sheet2 but when it gets to the point where it needs to copy the data from sheet6 I get a subscript out of range error. I have tried to use ActiveWorksheet in my code but then I get an object required error. I need some guidance on this please?

Here, You can see the names of the sheets as shown in the Project Explorer:

enter image description here

Upvotes: 0

Views: 62

Answers (1)

Abdallah El-Yaddak
Abdallah El-Yaddak

Reputation: 466

There is a difference between a sheet's name and a sheet's object name. For example, you can see in this pricture that I have a sheet named "Sheet2" while its object's name is "Sheet5"

enter image description here

To refer to that sheet, you have to do one of the following:

1: Use the sheet name: Sheets("Sheet2")

2: Use the sheet's object name: Sheet5

3: Use the sheet's number (position): Sheets(2)

(The sheet's position will change when you remove sheets that are before this sheet. [For example, when you delete the first sheet, the sheet that was second will now be the first] So, be careful when you use this method.)

So, in your situation, you need to make sure you have a sheet named "Sheet6", or name it correctly.

Another issue you have is that you are trying to copy a set of whole columns (A:Q) to a position that is not a whole columns, but a part of a column starting from row number 2 (A2).

So, you have to do something like this:

Sub AImportData()
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-3 'Do you really need this?
    With Sheet6
        maxRow = .UsedRange.Rows.Count + .UsedRange.Row - 1
        .Range("A2:Q" & maxRow).Copy Range("A2")
    End With
End Sub

Upvotes: 1

Related Questions