G
Guest
I need a little help with a macro I've created. I currently have a form with
check boxes indicating months and fiscal years. For example a user can click
the "july" (Checkbox1) and "FY05"(OptionsButton1) and then click a button
that automatically saves the file "September 2005 GL UPLOAD.csv" to a
location that I already specified in code. I used a multiple condition "If
then" statement to accomplish my task. The following code is a example of
this:
If CheckBox1.Value = True Then
If OptionButton1.Value = True Then
Sheets("54040 MONTHLY UPLOAD DATA").Select
Sheets("54040 MONTHLY UPLOAD DATA").Copy
ChDir _
"H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY 2005 uploaded
files"
ActiveWorkbook.SaveAs Filename:= _
"H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY 2005 uploaded
files\September 2005 GL UPLOAD.csv" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End If
End If
I basically copied this code numerous times, just changing the object names
and file names to make the macro work properly.The macro works fine, however,
it's not the most effecient macro. I had to write the code (above) 24 times
to accomadate just two fiscal years. My boss wants me to find a more
effecient way of accomplishing this task. He suggested looking into
declaring variables for the file names so when a user clicks a checkbox the
program will automatically know the naming of the file (and where) to save
the file. Can anyone help me with this, or at least point me in the right
direction? I have moderate excel programming skills and limited knowledge on
using variables.
Any information would be appreciated
check boxes indicating months and fiscal years. For example a user can click
the "july" (Checkbox1) and "FY05"(OptionsButton1) and then click a button
that automatically saves the file "September 2005 GL UPLOAD.csv" to a
location that I already specified in code. I used a multiple condition "If
then" statement to accomplish my task. The following code is a example of
this:
If CheckBox1.Value = True Then
If OptionButton1.Value = True Then
Sheets("54040 MONTHLY UPLOAD DATA").Select
Sheets("54040 MONTHLY UPLOAD DATA").Copy
ChDir _
"H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY 2005 uploaded
files"
ActiveWorkbook.SaveAs Filename:= _
"H:\QuickBooks\Finance\MIP\MONTHLY GL UPLOAD FILES\FY 2005 uploaded
files\September 2005 GL UPLOAD.csv" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End If
End If
I basically copied this code numerous times, just changing the object names
and file names to make the macro work properly.The macro works fine, however,
it's not the most effecient macro. I had to write the code (above) 24 times
to accomadate just two fiscal years. My boss wants me to find a more
effecient way of accomplishing this task. He suggested looking into
declaring variables for the file names so when a user clicks a checkbox the
program will automatically know the naming of the file (and where) to save
the file. Can anyone help me with this, or at least point me in the right
direction? I have moderate excel programming skills and limited knowledge on
using variables.
Any information would be appreciated