Copy PasteSpecial Question

NinaE_11

New Member
Joined
Aug 18, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
Hello,
I've tried to research this, but I'm lost as to why I can't get my copy/paste special VBA language to work. I am attempting to copy columns of data from one worksheet (wsCopy - my working spreadsheet that contains calculations and If Statements) to my destination worksheet (which has the company approved font formatting, etc.) that will eventually go into a Power Point. I seem to be able to copy over text, however with products of an IF statement, the numbers will not copy over.

Original data (sample IF Statement in the cell ) =IF(AI8/E8>70,70,IF(AI8/E8<-70,-70,AI8/E8)) - in this instance, the return is 67. I'm trying to copy that 67 and place it into my destination worksheet (wsDest). However, no luck. I'm not sure if I'm writing code wrong for the 'last row' portion of the VBA language, or if I need to set a variable of some sort? Here is my code so far:

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim CopyLastRow As Long
Dim DestLastRow As Long

Set wsCopy = ThisWorkbook.Worksheets(2)
Set wsDest = ThisWorkbook.Worksheets(1)

'1. Find last used row in the copy range based on data in column A
CopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
DestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row

'3. Clear contents of existing data range
wsDest.Range("A6:K" & DestLastRow).ClearContents

'4. Copy & Paste Data
wsCopy.Range("O8" & lCopyLastRow).Copy
wsDest.Range("G6").PasteSpecial Paste:=xlPasteValues

End Sub

For whatever reason, I can't get the numbers in Column O to copy over.

I'd sincerely appreciate any guidance - thank you!
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,299
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Just to point out before it gets lost with the discussion on the row number that as @alansidman pointed out your variable name should be CopyLastRow as declared and not lCopyLastRow.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,024
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad we were able to help.
 

NinaE_11

New Member
Joined
Aug 18, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
Just to point out before it gets lost with the discussion on the row number that as @alansidman pointed out your variable name should be CopyLastRow as declared and not lCopyLastRow.
Thank you, I will definitely clean that up!
 

NinaE_11

New Member
Joined
Aug 18, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
Looks like you have an extra character in this row

VBA Code:
wsCopy.Range("O8" & lCopyLastRow).Copy

I think it should be

VBA Code:
wsCopy.Range("O8" & CopyLastRow).Copy
Thank you for the sharp eye on this! I was caught in the weeds and overlooked this - I will clean up. Appreciate your help!
 

Forum statistics

Threads
1,147,452
Messages
5,741,206
Members
423,648
Latest member
steel1968

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
Top