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

"Miguel" skrev i meddelelsen
...
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