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

 

[chrisignm]

True, I didn't see that But it seems to be okay. The values are inserted properly, I don't have too much formation anyway.

This is the code that works for me (I don't like "Select" so much, but well )

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
Range("K13").Value = "WORKS!!!"
Worksheets("Update").UsedRange.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Close False
Worksheets("Rawdata").Cells.ClearContents
Worksheets("Rawdata").Range("A1").Select
ActiveSheet.Paste
End Sub

Thanks for your help!

 

[FormR]

Thanks for your help!

No problem.

Just a thought, have you considered adding a sheet to your existing workbook to do your processing and then deleting it after - it seems a little heavy handed creating a new workbook each time.

Something like this maybe:

Sub Test()
Dim ws As Worksheet
Set ws = Worksheets.Add(After:=Worksheets(1))
With ws
    .Range("A1").Value = "test"
    .Range("A2").Value = "test2"
    .Range("B5").Value = "test3"
    .Range("C2").Value = "C2"
    .Range("E5").Value = 1
End With
Worksheets("Rawdata").Cells.ClearContents
ws.UsedRange.Cells.Copy
Worksheets("Rawdata").Range("A1").PasteSpecial xlPasteValues
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End Sub

 

[chrisignm]

The thing is I have so many formulas inside that are connected to a dynamic linked range... that's why I have to do like that unfortunately (discussed that already through the last days, there's no way around that, as manual calculation has no effect on formula/named range combination)

 

[FormR]

Ah, I remember reading that thread! Good luck with the rest of your project.

 

[chrisignm]

Thanks and thank you again for helping me one step further