Paste special not working - sending me to debug

sdelan

New Member
Joined
May 23, 2022
Messages
5
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top