Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change workbook name to a generic name
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change workbook name to a generic name
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change workbook name to a generic name
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change workbook name to a generic name
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("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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Any way to change generic icons in quick access toolbar? | Excel Worksheet Functions | |||
Change a specific code to generic in VB macro | Excel Discussion (Misc queries) | |||
Generic Window/Workbook text | Excel Programming | |||
Generic Workbook Select | Excel Programming | |||
Generic ComboBox change event | Excel Programming |