Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I direct a macro to a newly opened workbook
A Macro in my MasterFile.XLS opens a New Workbook and pastes a copy of one of
the sheets into the new workbook. I want to then return to MasterFile.xls, clear contents in cell C6 of the Input page and then return to the newly created workbook. Whilst in the abbreviated coding below, the new book is Book12, it could of course be any number depending on how many times I run the process in the course of the day. After creating the new workbook, it gets saved with a name so there is only ever one new workbook open at a time but with a variable number. How do I achieve this ? Cells.Select Selection.Copy Workbooks.Add Template:="Workbook" Cells.Select ActiveSheet.Paste Windows("MasterFile.xls").Activate Sheets("Input Page").Select Range("C6").Select Application.CutCopyMode = False Selection.ClearContents Windows("Book12").Activate Range("F31").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I direct a macro to a newly opened workbook
Something like this should do
dim wbkNew as workbook set wbknew = Workbooks.Add Template:="Workbook" thisworkbook.Sheets("Sheet1").Cells.copy Destination:= _ wbknew.sheets("Sheet1").Cells thisworkbook.Sheets("Input Page").Range("C6").ClearContents Range("F31").Select -- HTH... Jim Thomlinson "dave caizley" wrote: A Macro in my MasterFile.XLS opens a New Workbook and pastes a copy of one of the sheets into the new workbook. I want to then return to MasterFile.xls, clear contents in cell C6 of the Input page and then return to the newly created workbook. Whilst in the abbreviated coding below, the new book is Book12, it could of course be any number depending on how many times I run the process in the course of the day. After creating the new workbook, it gets saved with a name so there is only ever one new workbook open at a time but with a variable number. How do I achieve this ? Cells.Select Selection.Copy Workbooks.Add Template:="Workbook" Cells.Select ActiveSheet.Paste Windows("MasterFile.xls").Activate Sheets("Input Page").Select Range("C6").Select Application.CutCopyMode = False Selection.ClearContents Windows("Book12").Activate Range("F31").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I direct a macro to a newly opened workbook
Right after:
Workbooks.Add Template:="Workbook" insert the line: s = ActiveWorkbook.Name and then instead of: Windows("Book12").Activate use: Windows(s).Activate This works because after you Add the new workbook, it becomes the Active workbook and we set the variable s to remember its name. -- Gary''s Student - gsnu200773 "dave caizley" wrote: A Macro in my MasterFile.XLS opens a New Workbook and pastes a copy of one of the sheets into the new workbook. I want to then return to MasterFile.xls, clear contents in cell C6 of the Input page and then return to the newly created workbook. Whilst in the abbreviated coding below, the new book is Book12, it could of course be any number depending on how many times I run the process in the course of the day. After creating the new workbook, it gets saved with a name so there is only ever one new workbook open at a time but with a variable number. How do I achieve this ? Cells.Select Selection.Copy Workbooks.Add Template:="Workbook" Cells.Select ActiveSheet.Paste Windows("MasterFile.xls").Activate Sheets("Input Page").Select Range("C6").Select Application.CutCopyMode = False Selection.ClearContents Windows("Book12").Activate Range("F31").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newly opened spreadsheet doesn't display in Excel 2007 | Excel Discussion (Misc queries) | |||
Allow user to choose worksheet from newly opened file | Excel Programming | |||
Close file and run macro from newly opened file | Excel Programming | |||
can't save newly opened files | Excel Discussion (Misc queries) | |||
Run a macro when a workbook is opened | Excel Programming |