Copy Paste area is not the same size VBA 1004 Error-CodePudding

This code is throwing error.

ActiveSheet.Range(Cells(14, 7), Cells(NewRow, 8)).Copy
ws.Range("A1").Offset(rn, 6).PasteSpecial xlPasteValues

Keeps on saying the size is not the same. Any suggestions?

CodePudding user response：

You can (re-)use a generic sub to copy values plus avoid copy/paste --> copyRangeValues

Using the generic sub avoids a possible error when resizing the target range. If it is correct once it is correct for all other calls as well.

Plus another tip: use variable names that explain what the variable contains. rn is such a non-self-explaining variable ... Propably this is the source of your error ...

Option Explicit

Public Sub copyWhatever()

'ActiveSheet.Range(Cells(14, 7), Cells(NewRow, 8)).Copy
'ws.Range("A1").Offset(rn, 6).PasteSpecial xlPasteValues

Dim rgSource As Range
Set rgSource = ActiveSheet.Range(Cells(14, 7), Cells(NewRow, 8))

Dim cTarget As Range
Set cTarget = ws.Range("A1").Offset(rn, 6)

copyRangeValues rgSource, cTarget

End Sub


'This is the generic sub that can be re-used for all copy tasks
Public Sub copyRangeValues(rgSource As Range, cTarget As Range)
'cTarget = cell where the copied values should start = top left corner

With rgSource
    cTarget.Resize(.Rows.Count, .Columns.Count).Value2 = .Value2
End With

End Sub

CodePudding user response：

Use the Value property of the Range object to paste values.

Reference the "source" range and make sure the "target" one has the same number of rows and columns

With ActiveSheet.Range(Cells(14, 7), Cells(NewRow, 8))
    ws.Range("A1").Offset(rn, 6).Resize(.Rows.Count, .columns.Count).Value = .Value
End With

CodePudding user response：

I'm not sure exactly what's wrong. Possibly the parenthesis are at issue, or maybe you have all on the same line, or maybe you need to specify the sheet within the range?

Here's a sample of something simple (I didn't use your exact cells as I just wanted to test and be done).

Good luck.

Dim ws As Worksheet
Set ws = ActiveSheet
Range(ws.Cells(1, 1), ws.Cells(8, 2)).Copy
ws.Range("D1").PasteSpecial (xlPasteValues)