Excel VB Macro

[Jon Arbuckle]

Hello all:

I have created macro in Excel that will essentially open a new workbook, and
copy certain cells off of certain pages to the new workbook. At the end, it
gives the user a chance to save it.

The problem is that as it references the different workbooks (the new one
just opened, and the source workbook), it will have problems if the filename
of the source file is changed (because I have it as a static name), and the
new workbook name differs depending on how long/many workbooks are opened
(i.e. book1, book12, book14, etc.).

I would like to assign the source workbook a name via a variable, and do the
same with the exported workbook. Can you show me how to do this? I am
quite an amateur when it comes to VB macros, so if you could give me a
little detail, or even examples I would appreciate it.

I would need to know how to define the variable based on the different file
names, AND how to incoporate it in a line such as this:

Windows("MY WINDOWS SPREADSHEET.xls").Activate

 

[Nick Hodge]

Jon

The code you give is sparse, but the idea is to assign the opened workbook
to a 'workbook' variable, than when you refer to that variable you will
always be referring to that workbook, so

Sub ReferenceWorkbook()
Dim NewWb as Workbook
set NewWb=workbooks.add
'Now NewWb refers to the workbook you just added
Windows(NewWb.Name).activate
End Sub

--

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog

 

[Jon Arbuckle]

Hello.

Thanks for the help. I think this is defining the variable for the new
opened book. How would I define the variable for the currently opened book
(the source book)?

Also, is it necessary to do that Sub in the middle of my code (would be a
sub in a sub), since I am already running a macro? This would obviously
apply to the End Sub too.


Thanks

 

[Bob Phillips]

Don't use wb names, use wb objects as Nick showed you

Set thisWB = Activeworkbook
Set newWB = Workbooks.Add

and when you want to refer to the original workbook, use thiswB, such as

thisWB.Worksheets(1).Activate
ThisWB.Close

as examples

and the new workbook, use newWB, such as

thisWB.Worksheets(1).Range("A1:A10").Copy _
newWB.Worksheets("Master").Range("A1")


Just use this within your existing code

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

Bob showed you how to use a variable for the activeworkbook.

But if you want to refer to the workbook with the code, you can use
ThisWorkbook.

Thisworkbook.worksheets("sheet1").range("a1").value = "hi there"