Copying worksheet with macro

[Janelle S]

Hi - am hoping that someone can help out with this one.
I have a proforma workbook (referral form) that has a macro button that
saves into another workbook using cell references as the new workbook name
eg. Tom 1-Nov-08. The referral form then closes and the new worksheet stays
open.
The user will contiue entering data into the new workbook. The problem is
that there is another macro that copies and pastes data to a 'dump' file,
however this macro refers to the 'referral form' as the source workbook. I
will be creating new workbooks all the time from the 'referral form' all with
different workbook names.
I don't want to use the Personal.xls as several users will be using the
workbooks and they could be from different PCs.
Is there an active worksheet reference that I could use.
Hope this is clear. Using Excel 2003.

 

[Joel]

I don't use statements like this in my code because it is too many characters

Workbooks("Referal forms").Sheets("Sheet1")

Instead I will do this at the beginning of my macro

set REFBk = Workbooks("Referal forms")
set REFSht = REFBk.sheets("Sheet1")

then in my code use either

with REFSht
.Range("A1") = 1
end with

or
REFSht.Range("A1") = 1



This will allow me to change the name of the workbook or worksheet without
going through the entire code and change every reference to the workbook or
sheet name.

In your case you can do something similar by putting in the beginning of the
code

Set REFBk = ActiveWorkBook

Then refer to this workbook by REFBk


You could also make the code use both

if XYZ = 1 then
Set REFBk = Workbooks("Referal forms")
else
Set REFBk = ActiveWorkBook
end if

 

[ShaneDevenshire]

Hi,

"Is there an active worksheet reference"

yes,

ActiveWorkbook