Change workbook name to a generic name

M

Miguel

Hi,
I have a problem naming workbooks. I have a macro that if it runs, it will
create a new woorkbook and then the macro will look at the new workbook and
will run some other code. The problem is that I name the new workbook
"Book1". So if I run the macro again it will not work because Excel will open
a new workbook but it will be "book2". Is there a way to change my macro so
that it will look at the new workbook if it is "book1" or "book2" or "book3"
etc?

Thanks
Miguel
 
M

Miguel

By the way here's my code:
Application.Goto Reference:="INPUT"
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Selection.PasteSpecial Paste:=xlPasteColumnWidths
Application.CutCopyMode = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "input"
Range("D1:D3").Select
Range("D3").Activate
Windows("Test QPS Output for ADF Calc.xls").Activate
Range("C61").Select
Application.Goto Reference:="SUMMARYREPORT"
Selection.Copy
Windows("Book1").Activate
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Selection.PasteSpecial Paste:=xlPasteColumnWidths
Range("A1").Select
Application.CutCopyMode = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "summary"
Range("A1").Select
End Sub

The part that says:
Windows("Book1").Activate

That's where I have the problem.
Thanks
 
J

Jacob Skaria

It is always better to reference the workbook object..especially when you
work with multiple workbooks at a time...Try the below code.



Sub Macro()
Dim wb As Workbook

Application.Goto Reference:="INPUT"
Selection.Copy
Set wb = Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Selection.PasteSpecial Paste:=xlPasteColumnWidths
Application.CutCopyMode = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
wb.Sheets("Sheet1").Select
wb.Sheets("Sheet1").Name = "input"
Range("D1:D3").Select
Range("D3").Activate
Windows("Test QPS Output for ADF Calc.xls").Activate
Range("C61").Select
Application.Goto Reference:="SUMMARYREPORT"
Selection.Copy
wb.Activate
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Selection.PasteSpecial Paste:=xlPasteColumnWidths
Range("A1").Select
Application.CutCopyMode = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "summary"
Range("A1").Select
End Sub
 
P

Per Jessen

Hi

Assign the new workbook to an object variable:

Set wbB=workbooks.add

then use the variable for reference. With your code it could look like this:

Dim wbA As Workbook
Dim wbB As Workbook
Dim DestSh As Worksheet

Set wbA = ThisWorkbook

Range("Input").Copy
Set wbB = Workbooks.Add
Set DestSh = wbB.Worksheets("Sheet1")
With DestSh.Range("A1")
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteColumnWidths
End With

With DestSh.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
DestSh.PageSetup.PrintArea = ""
With DestSh.PageSetup
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
DestSh.Name = "input"
'Range("D1:D3").Select
DestSh.Range("D3").Activate

wbA.Activate

Range("SUMMARYREPORT").Copy
Set DestSh = wbB.Worksheets("Sheet2")
With DestSh.Range("A1")
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteColumnWidths
End With
Application.CutCopyMode = False
With DestSh.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
DestSh.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
DestSh.Name = "summary"


Regards,
Per
 
M

Miguel

It worked. Thanks a lot

Miguel

Per Jessen said:
Hi

Assign the new workbook to an object variable:

Set wbB=workbooks.add

then use the variable for reference. With your code it could look like this:

Dim wbA As Workbook
Dim wbB As Workbook
Dim DestSh As Worksheet

Set wbA = ThisWorkbook

Range("Input").Copy
Set wbB = Workbooks.Add
Set DestSh = wbB.Worksheets("Sheet1")
With DestSh.Range("A1")
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteColumnWidths
End With

With DestSh.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
DestSh.PageSetup.PrintArea = ""
With DestSh.PageSetup
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
DestSh.Name = "input"
'Range("D1:D3").Select
DestSh.Range("D3").Activate

wbA.Activate

Range("SUMMARYREPORT").Copy
Set DestSh = wbB.Worksheets("Sheet2")
With DestSh.Range("A1")
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteColumnWidths
End With
Application.CutCopyMode = False
With DestSh.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
DestSh.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
DestSh.Name = "summary"


Regards,
Per
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Turning off automatic calculation 0
Setting name value across workbooks 5
Copying a Name's Contents 8
Opening a list of Excel files and run a macro in each 17
Embedded Excel into Word with Tables 0
Opening a new workbook via a macro 2
Macro - New Workbook 3
Need Help with Macro Security 1

Top