Copy/Paste variable-defined range between sheets

user_4072512 0
Viewed 50

I have two sheets, Raw Data and a user-created varSheetName.

I dump a huge data extract into Raw Data, and extract columns (based on the header field, which I can find no problem) to the new sheet. The column number the header is found on is always going to be different, as will be the number of rows below it.
EX: I find the header "models/0/availability", on column ?, with ?? rows to copy to varSheetName.

Knowing the copy & paste column #, and the last row #, I run this code:

With ThisWorkbook.Sheets(varSheetName)
    .Range(.Cells(intPasteRow, intPasteCol), .Cells(intLastRow, intPasteCol)).Value = Worksheets("Raw Data").Range(.Cells(1, iCopyCol), .Cells(intLastRow, iCopyCol)).Value
End With

I get

Runtime 1004: Application-defined or object-defined error

vba excel
1 Answers

Copy a Range by Assignment

  • When you copy in this way you need to make sure that the number of rows and number of columns of both ranges is equal:

    intLastRow - IntPasteRow <> intLastRow - 1 ' unless IntPasteRow = 1

  • To avoid those long unreadable lines, use variables. That's what they're for.

The Code

Option Explicit

Sub CopyData()
    
    ' Workbook
    Dim wb As Workbook: Set wb = ThisWorkbook
    ' Source
    Dim sws As Worksheet: Set sws = wb.Worksheets("Raw Data")
    Dim srg As Range: Set srg = sws.Range(sws.Cells(1, iCopyCol), _
        sws.Cells(intLastRow, iCopyCol))
    ' Destination
    Dim dws As Worksheet: Set dws = wb.Worksheets(varSheetName)
    Dim dfCell As Range: Set dfCell = dws.Cells(intPasteRow, intPasteCol)
    Dim drg As Range: Set drg = dfCell.Resize(srg.Rows.Count)
    ' Copy.
    drg.Value = srg.Value
    
End Sub
user_98140690
Related