Copy PasteSpecial Question

[NinaE_11]

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]

Try

wsCopy.Range("O" & lCopyLastRow).Copy

rather than

wsCopy.Range("O8" & lCopyLastRow).Copy

 

[alansidman]

Looks like you have an extra character in this row

wsCopy.Range("O8" & lCopyLastRow).Copy

I think it should be

wsCopy.Range("O8" & CopyLastRow).Copy

 

[MARK858]

Well spotted @alansidman although I still think the 8 needs removing.

 

[Joe4]

Well spotted @alansidman although I still think the 8 needs removing.

Absolutely!

Otherwise if "CopyLastRow" is something like "10", that range is going to end up being "O810", and not "O10"!

 

[alansidman]

Missed that part as I focused on the variables.

 

[NinaE_11]

Try

wsCopy.Range("O" & lCopyLastRow).Copy

rather than

wsCopy.Range("O8" & lCopyLastRow).Copy

Well there are headers in the O column above cell O8 that I do not want to copy over. Data begins in O8.

 

[MARK858]

Please read what Joe4 wrote, in your original code you are appending an 8 to the front of the last row so if your last cell was A8 what you have wrote in your original code would mean that you are trying to copy cell O88.
You are only copying one cell in the code and the only way that would be above row 8 is if CopyLastRow was less than 8.

 

[Joe4]

Well there are headers in the O column above cell O8 that I do not want to copy over. Data begins in O8.

That doesn't matter. It is important to understand what you are actually doing here.
You are building the range reference. A range reference has two components, the column reference and the row reference.

The first thing your code is doing is to find the last row in column A with data. That value is being stored in "lCopyLastRow".
So, if you want to copy the last row in column O, the range you are building would be:
"O" & lCopyLastRow

Let's walk through a simple example. Let's say that the last row is 50.
Then, this is what we would have as the range we want to copy:
"O" & lCopyLastRow
"O" & 50
"O50"

Now, if you left it the other way, this is what you would have:
"O8" & lCopyLastRow
"O8" & 50
"O850"

This would copy cell "O850"! This is clearly NOT what you want!

 

[NinaE_11]

That doesn't matter. It is important to understand what you are actually doing here.
You are building the range reference. A range reference has two components, the column reference and the row reference.

The first thing your code is doing is to find the last row in column A with data. That value is being stored in "lCopyLastRow".
So, if you want to copy the last row in column O, the range you are building would be:
"O" & lCopyLastRow

Let's walk through a simple example. Let's say that the last row is 50.
Then, this is what we would have as the range we want to copy:
"O" & lCopyLastRow
"O" & 50
"O50"

Now, if you left it the other way, this is what you would have:
"O8" & lCopyLastRow
"O8" & 50
"O850"

This would copy cell "O850"! This is clearly NOT what you want!

OH, that makes so much sense! Thank you!