Open new workbook => do some stuff => copy it => close "new" workbook without saving ?

[chrisignm]

Hello. I want to do the following from an existing workbook:

1) Open new workbook
2) Do some stuff (for now its just inserting some random values)
3) Copy the sheet (I just need the values)
4) Close the new workbook WITHOUT saving
5) Insert values in the existing workbook

My attempt didn't work:

Sub NewWorkbookToOldWorkbook()
Workbooks.Add
Worksheets.Add(After:=Worksheets(1)).Name = "Update"
Range("A1").Value = "test"
Range("A2").Value = "test2"
Range("B5").Value = "test3"
Range("C2").Value = "C2"
Range("E5").Value = 1
Worksheets("Update").Copy
ActiveWorkbook.Close savechanges:=False ' Also tried ActiveWorkbook.Close False
'Worksheets("Rawdata").Cells.ClearContents
'Worksheets("Rawdata").Range("A1").PasteSpecial xlPasteValues
End Sub

The first issues I'm running into: Doesn't close the workbook! And If I do it without "False" it asks me to save it.

Please Help

 

[FormR]

1) Open new workbook
2) Do some stuff (for now its just inserting some random values)
3) Copy the sheet (I just need the values)
4) Close the new workbook WITHOUT saving
5) Insert values in the existing workbook

Hi - where do you want to copy the sheet to? Is it into the workbook where the code is?

It looks like your code is creating a copy as a new workbook and then closing it (as it is the active workbook) leaving the original newly created workbook open.

 

[chrisignm]

Hey, I don't really understand what you mean. But maybe I try to explain it again.

What I would like to do is:

1) Open existing workbook, execute macro from there
Macro does the following:
2) Create/Open new workbook
3) Upload data from the internet inside there (Sheet "Upload") and do some changes on the data (I already got that working)
4) Copy all the values of "Upload"
5) Close the workbook that was opened in step 2 again. (don't save)
6) Delete the VALUES of sheet "RawData" in the existing workbook of step 1
7) Insert the values of step 4

Hope it is clear

My assumption is that the copied values of step 4 don't get lost with step 5. I hope that this is true.

Thanks a lot!

 

[FormR]

This

Worksheets("Update").Copy

creates a copy of the worksheet in a new workbook. It doesn't copy the values to the clipboard.

 

[chrisignm]

OK, I think its Worksheets("Update").UsedRange.Copy then?

Edit: works Will post the code in a second
Thanks already for the hint!

Edit2:

Sub NewWorkbookToOldWorkbook()Workbooks.Add
Worksheets.Add(After:=Worksheets(1)).Name = "Update"
Range("A1").Value = "test"
Range("A2").Value = "test2"
Range("B5").Value = "test3"
Range("C2").Value = "C2"
Range("E5").Value = 1
Worksheets("Update").UsedRange.Copy
ActiveWorkbook.Close False
Worksheets("Rawdata").Cells.ClearContents
Worksheets("Rawdata").Range("A1").PasteSpecial xlPasteValues

End Sub

The very last code doesn't work => pasting the values
Runtime Error 1004 => pastespecial method of range class failed

It has the values copied to the clipboard! If I do manually Ctrl+V after macro failed, it pastes the values I want.

 

[FormR]

This:

Worksheets("Rawdata").Cells.ClearContents

Clears the clipboard, perhaps perform this step before the copy.

 

[chrisignm]

This:

Worksheets("Rawdata").Cells.ClearContents

Clears the clipboard, perhaps perform this step before the copy.

It has the values copied to the clipboard! If I do manually Ctrl+V after macro failed, it pastes the values I want.

However I tried your suggestion, still got the error on the last line.

 

[FormR]

It appears that you can not use xlPasteValues after you have closed the source workbook.

 

[chrisignm]

The problem seems to be with the range.

If I change the last line to that:
Worksheets("Rawdata").Range("A1:B2").PasteSpecial Paste:=xlPasteAll

It works. But it will only paste values from this range (so everything outside will not get paste)

How could I set the range of Worksheets("Update").UsedRange.Copy = Range in "Rawdata"?

 

[FormR]

This works for me:

Worksheets("Rawdata").Range("A1").PasteSpecial Paste:=xlPasteAll

xlPasteValues does not.