Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying worksheet with macro
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying worksheet with macro
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 "Janelle S" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying worksheet with macro
Hi,
"Is there an active worksheet reference" yes, ActiveWorkbook -- Thanks, Shane Devenshire "Janelle S" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying worksheet cells into another worksheet using autofill | Excel Discussion (Misc queries) | |||
Copying the active worksheet to another worksheet in the same work | Excel Worksheet Functions | |||
Macro for Copying Worksheet to New Workbook | Excel Discussion (Misc queries) | |||
Looking up a variable in one worksheet and copying information from another column to another worksheet?? | Excel Discussion (Misc queries) | |||
Copying a worksheet witrh protected cells to a new worksheet | Excel Worksheet Functions |