Copy cells to another workbook and paste special

[winstela]

Hi All,

Hope some can help me, I am trying to copy cells to another workbook but cannot figure out how to just paste the value and format and not the formula

I have tried paste special but I cant seem to make it work but keep getting a compile error

.PasteSpecial Paste:=xlPasteValues

Can you point me in the right direction?

Sub Copy_Paste_Below_Last_Cell()

'Find the last used row in both sheets and copy and paste data below existing data.

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

Workbooks.Open "\\mcuk-adc\Prod_Results\01 Value Stream\50 VSM\2022\Kaizen Events\01 SWFF CR5000 Validator\Performance Boards\Daily Output - ABC.xlsx"

'Set variables for copy and destination sheets
Set wsCopy = Workbooks("Perfomance Board New .xlsm").Worksheets("Template")
Set wsDest = Workbooks("Daily Output.xlsx").Worksheets("Data")




'1. Find last used row in the copy range based on data in column A
'lCopyLastRow = 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
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1).Row

'3. Copy & Paste Data
wsCopy.Range("t3:t3").Copy _
wsDest.Range("A" & lDestLastRow)
wsCopy.Range("n25:u25").Copy _
wsDest.Range("g" & lDestLastRow)
wsCopy.Range("n5:n5").Copy _
wsDest.Range("r" & lDestLastRow)
wsCopy.Range("g25:h25").Copy _
wsDest.Range("b" & lDestLastRow)
wsCopy.Range("i25:i25").Copy _
wsDest.Range("f" & lDestLastRow)


End Sub

 

[sijpie]

Hi Winstela. Next time please put your code between VBA tags: press the little VBA icon above the area where you are typing. Then paste your code.

Copy and paste is very slow in code. In particular if you just want the values, then just tell VBA that the value in range( x ).value = range( y).value
That is a much faster way of copying values.

So your code would become:

'3. Copy & Paste Data
    wsDest.Range("A" & lDestLastRow).Value = wsCopy.Range("t3").Value

    wsDest.Range("g" & lDestLastRow & ":N" & lDestLastRow).Value = wsCopy.Range("n25:u25").Value

    wsDest.Range("r" & lDestLastRow).Value = wsCopy.Range("n5").Value

    wsDest.Range("b" & lDestLastRow).Value = wsCopy.Range("g25").Value

    wsDest.Range("f" & lDestLastRow).Value = wsCopy.Range("i25").Value

 

[winstela]

Hi Winstela. Next time please put your code between VBA tags: press the little VBA icon above the area where you are typing. Then paste your code.

Copy and paste is very slow in code. In particular if you just want the values, then just tell VBA that the value in range( x ).value = range( y).value
That is a much faster way of copying values.

So your code would become:

'3. Copy & Paste Data
    wsDest.Range("A" & lDestLastRow).Value = wsCopy.Range("t3").Value

    wsDest.Range("g" & lDestLastRow & ":N" & lDestLastRow).Value = wsCopy.Range("n25:u25").Value

    wsDest.Range("r" & lDestLastRow).Value = wsCopy.Range("n5").Value

    wsDest.Range("b" & lDestLastRow).Value = wsCopy.Range("g25").Value

    wsDest.Range("f" & lDestLastRow).Value = wsCopy.Range("i25").Value

Sorry about the code tags,, it was the first time I had posted for a while and I forgot, but thanks for the kind reminder.
I managed to figure it out, but could not get the range G to N to to copy as a whole range, but now I can see how to do it from your code above.

Thank you