Change workbook name to a generic name
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
--
If this post helps click Yes
---------------
Jacob Skaria
"Miguel" wrote:
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
"Miguel" wrote:
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
|