Copying and pastiing cell values

[LAVA2]

Hi,

I would like to request for some assistance in two tasks. First is to copy the cell values and formats from a workbook and secondly paste the cell values along with the formats into another workbook.

For example the code should be able to copy the cell values from workbook C:\CALS\FIRST RUNNER.xlsm, the cell values are in the range AD2:AG16 of sheet1(2). And secondly the code
should be able to paste those cell values along with the formats into Sheet1 of workbook C:\CALS\RUNNER.xlsm where the range for pasting the values and the formats is A2:D16.

Thanks

[Eastw00d]

Hi, you could use for example this code:

Please Login or Register  to view this content.

Cheers
Erwin

[LAVA2]

Hi thanks for the reply, i missed to mention about pasting formats can u assist me in that too?

[Eastw00d]

you can change xlPasteValues into xlPasteValuesAndNumberFormats
Cheers
Erwin

[LAVA2]

Again Thank you very much Erwin

[xladept]

This should also work:

Please Login or Register  to view this content.

[xladept]

@ Erwin - I was working on the duplicate post and only posted so the time spent wouldn't be wasted.

And, thanks for the rep!

[LAVA2]

Sub CopyPaste()
Dim wsc As Worksheet, wst As Worksheet
Dim wbc As Workbook, wbt As Workbook
Set wbc = Workbooks.Open("C:\CALS\FIRST RUNNER.xlsm")
Set wsc = Worksheets("sheet1(2)")
Set wbt = Workbooks.Open("C:\CALS\RUNNER.xlsm")
Set wst = Worksheets("Sheet1")
wsc.Range("AD2:AG16").Copy
wst.Range("A2").PasteSpecial xlPasteValues
wbt.Save
wbt.Close
wbc.Close
End Subthis code shows error pls help

[Eastw00d]

Exactly where shows the error, at what command/line?
And I see a difference in the original code at the "Set wst" line. In the original code this was:

Please Login or Register  to view this content.

Cheers
Erwin

[LAVA2]

the error is at :

Set wsc = Worksheets("sheet1(2)")

[Eastw00d]

Still an error if you do this:?

Please Login or Register  to view this content.

Please note the space between 1 and (
Cheers
Erwin

[LAVA2]

What if the workbook is already open can u suggest an edit in this one

Sub CopyPaste()
Dim wsc As Worksheet, wst As Worksheet
Dim wbc As Workbook, wbt As Workbook
Set wbc = Workbooks("C:\CALS\FIRST RUNNER.xlsm")
Set wsc = Worksheets("sheet1 (2)")
Set wbt = Workbooks("C:\CALS\FIRST RUNNER.xlsm")
Set wst = Worksheets("Sheet1 (2)")
wsc.Range("AD2:AG16").Copy
wst.Range("A2").PasteSpecial xlPasteValues
wbt.Save
wbt.Close
wbc.Close
End Sub

[Eastw00d]

It's very difficult to read this, please use code tags. (the hash-tag) in the edit-screen
Cheers
Erwin

[Eastw00d]

Please Login or Register  to view this content.