Paste special not working - sending me to debug

[sdelan]

Code is below, can anyone tell me why paste special is not working...? I use this same code with xrange and it works fine, then I try to copy paste another set, and I get an error when it gets to
Selection.PasteSpecial xlPasteValues





LastRow = Cells(Rows.Count, 1).End(xlUp).Row 'Gets the lastrow in the active column
LastUsedColumn = Cells(1, Columns.Count).End(xlToLeft).Column + 1 'remove the +1 will go to the last used column

'Code to copy everything from Calculations to the selected Month
Worksheets("Calculations").Activate
Set yrange = Range(Cells(1, LastUsedColumn), Cells(LastRow, 2)) 'set copy range
yrange.Copy
'xrange.select use this to see selection

Worksheets(myValue).Activate 'set worksheet based on user input
LastUsedColumnPaste = Cells(1, Columns.Count).End(xlToLeft).Column + 1 'remove the +1 will go to the last used column
Set yrange = Range(Cells(1, LastUsedColumnPaste), Cells(1, LastUsedColumnPaste)) 'set paste range
yrange.PasteSpecial
Selection.PasteSpecial xlPasteValues
Selection.PasteSpecial xlPasteColumnWidths

 

[6StringJazzer]

What error number and error message are you getting?

 

[Zot]

Avoid using Activate unless cannot be avoided. Error may occur because the macro can get confused the range you defined refers to which worksheet.

For example, define sheet into variable like:
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = ActiveWorkbook.Sheets("Calculations")
Set ws2 = ActiveWorkbook.Sheets(myValue)

Set xrange = ws1.Range("A1", "D4")
xrange.Copy ws2.Range("C1") --------- Copy xrange on ws1 and paste to range("C1") on ws2.

I could not understand what you were trying to do. You use same yrange for both copy and paste destination?

 

[sdelan]

What error number and error message are you getting?

Okay, well I wouldn't be able to tell you, I opened it today, and it was working. If it happens again I will take a screenshot.

 

[sdelan]

Avoid using Activate unless cannot be avoided. Error may occur because the macro can get confused the range you defined refers to which worksheet.

For example, define sheet into variable like:
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = ActiveWorkbook.Sheets("Calculations")
Set ws2 = ActiveWorkbook.Sheets(myValue)

Set xrange = ws1.Range("A1", "D4")
xrange.Copy ws2.Range("C1") --------- Copy xrange on ws1 and paste to range("C1") on ws2.

I could not understand what you were trying to do. You use same yrange for both copy and paste destination?

Hi,

All I am trying to do with this code is copy a range of data from one sheet to another. I Don't want to define the range the way you have it specified because it limits my functions. I sometimes add columns with data, and I would have to adjust the code each time if I use the proposed method. With the code I have, the range is defined based on the last cell/column used, so it automatically selects the range based on the amount of data and columns I have.

 

[Zot]

Hi,

All I am trying to do with this code is copy a range of data from one sheet to another. I Don't want to define the range the way you have it specified because it limits my functions. I sometimes add columns with data, and I would have to adjust the code each time if I use the proposed method. With the code I have, the range is defined based on the last cell/column used, so it automatically selects the range based on the amount of data and columns I have.

I was just giving example. You first 2 lines
LastRow = Cells(Rows.Count, 1).End(xlUp).Row 'Gets the lastrow in the active column
LastUsedColumn = Cells(1, Columns.Count).End(xlToLeft).Column + 1 'remove the +1 will go to the last used column

The LastRow or the LastUsedColumn will refers to the value in current active sheets. If you want to read the Calculation sheet but run the macro while looking at other sheet then the values would not be the for the Calculation sheet. You will get wrong values. That was why I gave example to provide the sheet reference name. By doing so, you can run macro regardless which sheet is currently active, It will refer to correct sheet.

You defined
Set yrange = Range(Cells(1, LastUsedColumn), Cells(LastRow, 2)) 'set copy range

then activate sheet
Worksheets(myValue)

The use the same name yrange for paste location
Set yrange = Range(Cells(1, LastUsedColumnPaste), Cells(1, LastUsedColumnPaste)) 'set paste range

Why is that? That may cause your error perhaps.