I hope this is an easy one. I have two sheets, Raw Data and a user-created one varSheetName. I am dumping a huge data extract into Raw Data, and extracting 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 i t. EX: I find the header "models/0/availability", on column ?, with ?? rows I need to copy to varSheetName.

Knowing the copy & paste column #, and the last row #, I am running 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

The error I get is Runtime 1004: Application-defined or object-defined error. Again, I hope this is just an easy "your syntax is bad." I've been fighting with this all morning, and instead of beating my head against the wall, I'm admitting defeat and asking for help!

Thanks!

CodePudding user response：

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