How do I direct a macro to a newly opened workbook

[dave caizley]

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

 

[Jim Thomlinson]

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

 

[Gary''s Student]

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.