transfer data then transfer some back

[rhombus4]

have a spreadsheet called Book1, then transfer data to Test.

In test it calcualtes some figures which i then want to transfer back to Book1 before closing Test. Not sure where to put the code to copy from Test.xls Sheet3 A1:D1 then copy it to the initial Book.xls Sheet 1 A30:d30


NewName = ActiveWorkbook.Name

Workbooks.Open "C:My Docs\Test.xls.xls"


Workbooks(NewName).Sheets("Sheet1").Range("A101:C101").Copy
Workbooks("Test.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues


'Save and close Test Spreadsheet

ActiveWorkbook.Save
ActiveWindow.Close

Workbooks(NewName).Activate
CutCopyMode = False
ThisWorkbook.Activate
Sheets("Sheet1").Select
Range("A1").Select



End Sub

 

[VoG]

Try this

Workbooks(NewName).Sheets("Sheet1").Range("A101:C101").Copy
Workbooks("Test.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Workbooks("Test.xls").Sheets("Sheet3").Range("A1:D1").Copy Destination:=Workbooks(NewName).Sheets("Sheet1").Range("A30")

 

[rhombus4]

Try this

Workbooks(NewName).Sheets("Sheet1").Range("A101:C101").Copy
Workbooks("Test.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Workbooks("Test.xls").Sheets("Sheet3").Range("A1:D1").Copy Destination:=Workbooks(NewName).Sheets("Sheet1").Range("A30")

It transfers it back but just the formula, just need the answer. prob need paste special values but not sure where to add it?

 

[VoG]

Try

Workbooks(NewName).Sheets("Sheet1").Range("A101:C101").Copy
Workbooks("Test.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Workbooks("Test.xls").Sheets("Sheet3").Range("A1:D1").Copy
Workbooks(NewName).Sheets("Sheet1").Range("A30").PasteSpecial Paste:=xlPasteValues

 

[rhombus4]

Try

Workbooks(NewName).Sheets("Sheet1").Range("A101:C101").Copy
Workbooks("Test.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Workbooks("Test.xls").Sheets("Sheet3").Range("A1:D1").Copy
Workbooks(NewName).Sheets("Sheet1").Range("A30").PasteSpecial Paste:=xlPasteValues

the above doesnt transfer anything back the cells in Book1 are now blank

 

[VoG]

I just tried it and it worked for me.

 

[rhombus4]

I just tried it and it worked for me.

Sorry Peter, just checked it and works as you said Thanks