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
|