Passing File Name To Macro

[halem2]

Hi

I have two workbooks. One that always has the same name and the 2n
one that changes names constantly (I get tons of files through email)

How can I pass to a macro I have the name onf the workbook that alway
changes? In other words: I have the one that never changes open an
then I open the 2nd one, which always has a different name. When I ru
my macro, I need to be able to pass to the macro the name f the secon
workbook I just open

thanks in advance, :

 

[Nick Hodge]

There are tons of ways

If you can be sure it is the activeworkbook you can use

Activeworkbook.Name

etc. You are best to set an object variable to it at that point and then it
is irrelevant if it remains the active workbook, eg

Dim wb as workbook
Set wb = ActiveWorkbook
wb.name
wb.save
wb.close
'etc
'etc

Also you can get the file name using

Application.GetOpenFilename

It doesn't open the file it simply assigns the file name and path to a
variable, but it displays the standard open file dialog

myFile=Application.GetOpenFilename()

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[halem2]

I dont think I explained myself properly. Two workbooks I NEED TO SET
THE ACTIVE ONE TO THJE ONE I DONT KNOW THE NAME OF.

wb1.xls - name never changes

?.xls - this name will be changing all the time since I have to work
with many workbooks.

I have a macro built to copy/paste from ?.xls to wb1.xls so in order to
copy and paste I have to make the ?.xls the active one. How can I code
it so when I run the macro, it either makes whatever name ?.xls has the
active wk or prompts me for the name of the ?.xls and passes it to the
macro as the active wk?

This is the code I have and even thou it worked, it just started
failing at the *** indicated below and I can't seem to be able to fix
it.

Sub PasteRanges()
'MsgBox "make sure the ACTIVE sheet is the SOURCE sheet!", vbOKCancel
Const rng1 = "D9"
Const rng2 = "O6:O8"
Const rng3 = "A17:A25"
Const rng4 = "G18:G25"
Const rng5 = "I16:AC16"
Const rng6 = "I21:AC25"
Const rng7 = "AG17"
Const rng8 = "AG21:AG26"
'
If ActiveWorkbook.Name <> "Forecast By Cost ALL Master Macro
5-31-06.xls" Then
'Assume ACTIVE sheet is SOURCE sheet
With ThisWorkbook.Worksheets(ActiveSheet.Name) ***** FAILS HERE
Range(rng1).Value = ActiveSheet.Range(rng1).Value
Range(rng2).Value = ActiveSheet.Range(rng2).Value
Range(rng3).Value = ActiveSheet.Range(rng3).Value
Range(rng4).Value = ActiveSheet.Range(rng4).Value
Range(rng5).Value = ActiveSheet.Range(rng5).Value
Range(rng6).Value = ActiveSheet.Range(rng6).Value
Range(rng7).Value = ActiveSheet.Range(rng7).Value
Range(rng8).Value = ActiveSheet.Range(rng8).Value
'.Range(rng9).Value = ActiveSheet.Range(rng9).Value
End With
Else: MsgBox "Click the other workbook.!", vbOKOnly
Exit Sub
'
End If
End Sub