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

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("D13").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

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("D13").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("D13").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

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("D13").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

"Miguel" <(E-Mail Removed)> skrev i meddelelsen
news:3EE7A2F9-E0D0-4D12-8ACD-(E-Mail Removed)...
> 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

It worked. Thanks a lot

Miguel

"Per Jessen" wrote:

> 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("D13").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
>
> "Miguel" <(E-Mail Removed)> skrev i meddelelsen
> news:3EE7A2F9-E0D0-4D12-8ACD-(E-Mail Removed)...
> > 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
>
>