VBA Error: Copy and paste area not the same size

[honeycake5505]

Hi, I am trying to record a macro of me copying and pasting. My code is below:

I am selecting one cell when I paste, but it is still giving me an error when I run the code. Any help is appreciated!

Range("A46:W46").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Spend Not Captured").Select
Range("H166").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=6
Range("A3661").Select
ActiveSheet.Paste

 

[Joe4]

Welcome to the Board!

Without seeing the data that you are working with, we cannot determine exactly what it is that is supposed to happen (as it is dependent on the data structure).

Can you provide a sample of your data and expected results?

Note that MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

There is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[jolivanes]

There is something different in your sheets to get that error. We unfortunately can't see that.
In the meantime, see if this does what you need. Result is values only.

Sub Maybe_Try()
Dim sh1 As Worksheet, sh2 As Worksheet, lr_sh1 As Long
Set sh1 = Worksheets("Sheet1")    '<---- Change to sheet name where the data is copies FROM
Set sh2 = Sheets("Spend Not Captured")
lr_sh1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row
    sh2.Range("H166").Resize(lr_sh1 - 45, 23).Value = sh1.Range("A46:W" & lr_sh1).Value
End Sub

 

[honeycake5505]

There is something different in your sheets to get that error. We unfortunately can't see that.
In the meantime, see if this does what you need. Result is values only.

Sub Maybe_Try()
Dim sh1 As Worksheet, sh2 As Worksheet, lr_sh1 As Long
Set sh1 = Worksheets("Sheet1")    '<---- Change to sheet name where the data is copies FROM
Set sh2 = Sheets("Spend Not Captured")
lr_sh1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row
    sh2.Range("H166").Resize(lr_sh1 - 45, 23).Value = sh1.Range("A46:W" & lr_sh1).Value
End Sub

Thank you for your response! I unfortunately can't share what the data sheets look like due to confidentiality of it, but I can share the entire code if that helps? If not, no worries, thanks again!

 

[honeycake5505]

Welcome to the Board!

Without seeing the data that you are working with, we cannot determine exactly what it is that is supposed to happen (as it is dependent on the data structure).

Can you provide a sample of your data and expected results?

Note that MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

There is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi, thank you for the welcome! I unfortunately can't provide a sample data due to confidentiality of it, but I can share the entire code if that helps? If not no worries, thanks again!

 

[jolivanes]

And what is the result of using the macro from Post #3?

A few remarks.
Selecting is very seldom needed. It slows down the running of a macro and it creates a jumping screen.

Selection.End(xlDown)

might give wrong results if there is a blank cell in the data.
A safer way to find the last used cell in a column is as in the code in the previous post. There are several more different ways. When to use them is sheet layout dependent.
What do you want the result of the 2nd time you use

Range(Selection, Selection.End(xlDown)).Select

to be?
Do you want the resulting range to be the same size as the copied range or do you want to go to the next available empty cell?
In the previous post I assumed that you wanted the data to be pasted in Range H166
The "Scroll Down" lines created by recording a macro can, and should be, deleted.

 

[Joe4]

Hi, thank you for the welcome! I unfortunately can't provide a sample data due to confidentiality of it, but I can share the entire code if that helps? If not no worries, thanks again!

Just replace the sensitive data with made-up data. The data is not really important to us, it is the location of it all that matters. Without knowing that, it is very difficult to try to help you (sort of like trying to fix a car over the telephone!).

Or explain exactly what your code is supposed to be doing in detail (so include range references).