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!
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!
