How do I use a variable name for a workbook in vba
I think this might help
Call GetContractFileName
Workbooks.Open
FileName:="G:\USER\Contracts\Data_Files\All_2004_C atalog.xls"
sFile = GetContractFileName
Sheets("Sheet1").Copy After:=Windows(sFile).Sheets(1)
Windows(sFile).Activate
are you sure about this next line, a sheet wouldn't normally have .xls
at the end
Sheets(sFile).Select
_______________________________________
Public Function GetContractFileName()
ContractFile = InputBox("Enter the Contract Number: ", "Contract File")
ContractNo=ContractFile
ContractFileName = ContractFile & ".xls"
End Function
_______________________________
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Emma" wrote in message
...
Here is what I am trying to do. We download information from our AS400
all
the time in particular a "contract file" I need to be able to create a
macro
that formats this contract file and performs several calculations on it.
I
am trying to do this via a macro button. I am having a problem when I
call
the file in my code.
Here is where I start having problems:
Call GetContractFileName
Workbooks.Open
FileName:="G:\USER\Contracts\Data_Files\All_2004_C atalog.xls"
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Windows("80266-00-0.xls").Sheets(1) 'here
is where I need to use the variable "ContractFileName" I just don't know
how
to do it.
Windows("80266-00-0.xls").Activate 'here also
Sheets("80266-00-0.xls").Select 'and here too
_______________________________________
Public Sub GetContractFileName()
ContractFile = InputBox("Enter the Contract Number: ", "Contract
File")
ContractNo=ContractFile
ContractFile = ContractFile & ".xls"
End Sub
_______________________________
Can someone help? Pleeease!
|