Open new workbook and paste to it

[EOAEvan]

I have a line of VBA code Workbooks.add to add a new book. What I want to accompish is to copy a sheet from another existing book (where the code is located) to the new workbook. Though the new workbook number can change. How can I get it to paste in the new book reguardless of the number its assigned?

 

[Domski]

Hi,

When you add the workbook it should become the active one so maybe:

Sub Copy_Sheet()
    
Workbooks.Add
ThisWorkbook.Sheets("Sheet2").Copy Before:=ActiveWorkbook.Sheets(1)
    
End Sub

Hope it helps,

Dom

 

[Cbrine]

You will need to learn a little about qualified references. Here's the base structure you will need to use.

Dim wb As Workbook, ws As Worksheet
Set wb = ActiveWorkbook
Set ws = ActiveSheet

Set pb = Workbooks.Add
Set ps = ActiveSheet

ws.Range("A1:B6").Copy ps.Range("A1")

Since you set the pb reference in the .add statement, it doesn't matter what the name of the file is.

HTH
Cal

 

[EOAEvan]

Thanks for the responces. both seem to work. here is what i have so far. New problem is that after i paste as value its changeing a long account number from 110240070000000 to 1.1024E+14. I need it to remain in the orginal format that shows the entire number.

Im sure there are better ways to do this. What I have works but if you want to tackle improving whats here im always up for learning a new/better way.

Sub HardCode()
Application.ScreenUpdating = False
Workbooks.Add
ThisWorkbook.Sheets("TOTALS").Copy Before:=ActiveWorkbook.Sheets(1)
ThisWorkbook.Sheets("Central").Copy after:=ActiveWorkbook.Sheets("Totals")
ThisWorkbook.Sheets("Florida").Copy after:=ActiveWorkbook.Sheets("Central")
ThisWorkbook.Sheets("South").Copy after:=ActiveWorkbook.Sheets("Florida")
ThisWorkbook.Sheets("West").Copy after:=ActiveWorkbook.Sheets("South")
ThisWorkbook.Sheets("East").Copy after:=ActiveWorkbook.Sheets("West")

ActiveWorkbook.Sheets("sheet1").Delete
ActiveWorkbook.Sheets("sheet2").Delete
ActiveWorkbook.Sheets("sheet3").Delete

ActiveWorkbook.Sheets("TOTALS").Range("a19:f30").Value = ActiveWorkbook.Sheets("TOTALS").Range("a19:f30").Value
ActiveWorkbook.Sheets("Central").Range("a24:f500").Value = ActiveWorkbook.Sheets("Central").Range("a24:f500").Value
ActiveWorkbook.Sheets("Florida").Range("a24:f500").Value = ActiveWorkbook.Sheets("Florida").Range("a24:f500").Value
ActiveWorkbook.Sheets("South").Range("a24:f500").Value = ActiveWorkbook.Sheets("South").Range("a24:f500").Value
ActiveWorkbook.Sheets("West").Range("a24:f500").Value = ActiveWorkbook.Sheets("West").Range("a24:f500").Value
ActiveWorkbook.Sheets("East").Range("a24:f500").Value = ActiveWorkbook.Sheets("East").Range("a24:f500").Value

ActiveWorkbook.Sheets("Totals").Range("F30") = ("=sum(F19:F29)")

ActiveWorkbook.Sheets("totals").Select
Range("a1").Select
Application.ScreenUpdating = True

End Sub

Thanks!

 

[Norie]

Why not just create a new workbook with the required worksheets rather than a blank one?

ThisWorkbook.Sheets(Array("TOTALS", "Central", "Florida", "South", "West", "East")).Copy

 

[EOAEvan]

Thanks Norie. That will help shorten the code. Though the actual format issue is still there. I know i can fix this by changing the format of the ranges but the only way i know how is to use .select and from reading there is always a better way than range.select etc.

 

[Norie]

Sorry I don't quite understand.

The code I posted shouldn't change any formatting.

 

[EOAEvan]

That line of code doesnt. It "copies/pastes" exactly what is on the ThisWorkbook. Though there are many formulas that i need to change to values only. When I change them to values using

ActiveWorkbook.Sheets("TOTALS").Range("a19:f30").Value = ActiveWorkbook.Sheets("TOTALS").Range("a19:f30").Value

it loses the formatting for some reason.

The way i have it posted above acutally keeps the formatting up until i change that range to values only.