Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy worksheet with formulas and vba function
hi all
thanks to everyone for their help so far! i have a vba project in excel that saves several sheets to a new workbook. thus far i was saving only the values using <BEGIN VBA CODE Worksheets(Array("Work Order", "Packing Slip", "Invoice", "Release", "Shipping", "Master Price List")).Copy Set wkbk = ActiveWorkbook For Each sh In wkbk.Worksheets With sh.UsedRange .Value = .Value End With Next <END VBA CODE almost everything seemed to work perfectly, however some cells in the saved (new) workbook will have #Name? instead of the value of the cell of the parent worksheet. the cells that generate the #Name? after being copied have the following formula in the master sheet cell <BEGIN EXCEL FORMULA =IF(ISBLANK(C27),"",chooselotnumber(VLOOKUP(C27,'M aster Price List'!$B$7:$O$44,10,FALSE))) <END EXCEL FORMULA the chooselotnumber function checks to see if there are multiple lot numbers for the choosen product. if there is only one, it returns that one. o/w it will prompt the user for which lot number to use. i would like to change my code to copy the formulas instead of just the values. (the new workbook will need to be opened and updated, so having the formulas there is a great help) also, i have a function in the master workbook that is used in some of the calculations. how do i modify the above code to copy the formulas, ( .FormulaR1C1 = ..FormulaR1C1 ?) and also copy the function i use to the new workbook? thanks! J |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy worksheet with formulas and vba function
Change:
<BEGIN VBA CODE Worksheets(Array("Work Order", "Packing Slip", "Invoice", "Release", "Shipping", "Master Price List")).Copy Set wkbk = ActiveWorkbook For Each sh In wkbk.Worksheets With sh.UsedRange .Value = .Value End With Next <END VBA CODE To sName = ActiveWorkbook.FullName sName = Left(sName,len(sName)-4) Application.DisplayAlerts = False ActiveWorkbook.SaveCopyAs sName & "AA.xls" Application.DisplayAlerts = True v = "#Work Order##Packing Slip##Invoice##Release#" _ & "#Shipping##Master Price List#" Set bk = Workbooks.Open SName & "AA.xls" for each sh in bk.Worksheets if instr(1,v,"#" & sh.Name & "#",vbTextCompare) = 0 then Application.DisplayAlerts = False sh.delete application.DisplayAlerts = True end if Next -- Regards, Tom Ogilvy "Gixxer_J_97" wrote in message ... hi all thanks to everyone for their help so far! i have a vba project in excel that saves several sheets to a new workbook. thus far i was saving only the values using <BEGIN VBA CODE Worksheets(Array("Work Order", "Packing Slip", "Invoice", "Release", "Shipping", "Master Price List")).Copy Set wkbk = ActiveWorkbook For Each sh In wkbk.Worksheets With sh.UsedRange .Value = .Value End With Next <END VBA CODE almost everything seemed to work perfectly, however some cells in the saved (new) workbook will have #Name? instead of the value of the cell of the parent worksheet. the cells that generate the #Name? after being copied have the following formula in the master sheet cell <BEGIN EXCEL FORMULA =IF(ISBLANK(C27),"",chooselotnumber(VLOOKUP(C27,'M aster Price List'!$B$7:$O$44,10,FALSE))) <END EXCEL FORMULA the chooselotnumber function checks to see if there are multiple lot numbers for the choosen product. if there is only one, it returns that one. o/w it will prompt the user for which lot number to use. i would like to change my code to copy the formulas instead of just the values. (the new workbook will need to be opened and updated, so having the formulas there is a great help) also, i have a function in the master workbook that is used in some of the calculations. how do i modify the above code to copy the formulas, ( .FormulaR1C1 = .FormulaR1C1 ?) and also copy the function i use to the new workbook? thanks! J |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy worksheet with formulas and vba function
Thank you Tom!
that was a huge help - i do have one question. does the copied workbook need to be opened in order to delete the extraneous sheets? i'm assuming that it does as just by looking at the code i think that it is just creating a copy of the active workbook (in order to copy all the vba code?) and not actually creating a new workbook and then copying. is that correct? and to close this workbook (saving changes) Application.DisplayAlerts = False bk.Close savechanges:=True Application.DisplayAlerts = True shoud save the changes, close it and not prompt the user for anything, correct? and finally - to select a particular sheet in the copied workbook (say "Work Order") can i select that with sheets("Work Order").select or do i need to do something diferent using bk? thanks again! (I guess that was more than one question... =) ) J "Tom Ogilvy" wrote: Change: <BEGIN VBA CODE Worksheets(Array("Work Order", "Packing Slip", "Invoice", "Release", "Shipping", "Master Price List")).Copy Set wkbk = ActiveWorkbook For Each sh In wkbk.Worksheets With sh.UsedRange .Value = .Value End With Next <END VBA CODE To sName = ActiveWorkbook.FullName sName = Left(sName,len(sName)-4) Application.DisplayAlerts = False ActiveWorkbook.SaveCopyAs sName & "AA.xls" Application.DisplayAlerts = True v = "#Work Order##Packing Slip##Invoice##Release#" _ & "#Shipping##Master Price List#" Set bk = Workbooks.Open SName & "AA.xls" for each sh in bk.Worksheets if instr(1,v,"#" & sh.Name & "#",vbTextCompare) = 0 then Application.DisplayAlerts = False sh.delete application.DisplayAlerts = True end if Next -- Regards, Tom Ogilvy "Gixxer_J_97" wrote in message ... hi all thanks to everyone for their help so far! i have a vba project in excel that saves several sheets to a new workbook. thus far i was saving only the values using <BEGIN VBA CODE Worksheets(Array("Work Order", "Packing Slip", "Invoice", "Release", "Shipping", "Master Price List")).Copy Set wkbk = ActiveWorkbook For Each sh In wkbk.Worksheets With sh.UsedRange .Value = .Value End With Next <END VBA CODE almost everything seemed to work perfectly, however some cells in the saved (new) workbook will have #Name? instead of the value of the cell of the parent worksheet. the cells that generate the #Name? after being copied have the following formula in the master sheet cell <BEGIN EXCEL FORMULA =IF(ISBLANK(C27),"",chooselotnumber(VLOOKUP(C27,'M aster Price List'!$B$7:$O$44,10,FALSE))) <END EXCEL FORMULA the chooselotnumber function checks to see if there are multiple lot numbers for the choosen product. if there is only one, it returns that one. o/w it will prompt the user for which lot number to use. i would like to change my code to copy the formulas instead of just the values. (the new workbook will need to be opened and updated, so having the formulas there is a great help) also, i have a function in the master workbook that is used in some of the calculations. how do i modify the above code to copy the formulas, ( .FormulaR1C1 = .FormulaR1C1 ?) and also copy the function i use to the new workbook? thanks! J |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy worksheet with formulas and vba function
Yes, the code opens the workbook to delete the sheets (already included -
but there was a typo which has been corrected). Other questions implemented as well. Assume questions were addressing the copy - the old workbook remains open - untouched. Option Explicit Sub CC() Dim sh As Worksheet, sName As String Dim v As String, bk As Workbook sName = ActiveWorkbook.FullName sName = Left(sName, Len(sName) - 4) Application.DisplayAlerts = False ActiveWorkbook.SaveCopyAs sName & "AA.xls" Application.DisplayAlerts = True v = "#Work Order##Packing Slip##Invoice##Release#" _ & "#Shipping##Master Price List#" ' ' Opens the copy Here ' Set bk = Workbooks.Open(sName & "AA.xls") For Each sh In bk.Worksheets If InStr(1, v, "#" & sh.Name & "#", vbTextCompare) = 0 Then Application.DisplayAlerts = False sh.Delete Application.DisplayAlerts = True End If Next Worksheets("Work Order").Select bk.Close SaveChanges:=True End Sub Anyway, it worked for me in light testing. -- Regards, Tom Ogilvy "Gixxer_J_97" wrote in message ... Thank you Tom! that was a huge help - i do have one question. does the copied workbook need to be opened in order to delete the extraneous sheets? i'm assuming that it does as just by looking at the code i think that it is just creating a copy of the active workbook (in order to copy all the vba code?) and not actually creating a new workbook and then copying. is that correct? and to close this workbook (saving changes) Application.DisplayAlerts = False bk.Close savechanges:=True Application.DisplayAlerts = True shoud save the changes, close it and not prompt the user for anything, correct? and finally - to select a particular sheet in the copied workbook (say "Work Order") can i select that with sheets("Work Order").select or do i need to do something diferent using bk? thanks again! (I guess that was more than one question... =) ) J "Tom Ogilvy" wrote: Change: <BEGIN VBA CODE Worksheets(Array("Work Order", "Packing Slip", "Invoice", "Release", "Shipping", "Master Price List")).Copy Set wkbk = ActiveWorkbook For Each sh In wkbk.Worksheets With sh.UsedRange .Value = .Value End With Next <END VBA CODE To sName = ActiveWorkbook.FullName sName = Left(sName,len(sName)-4) Application.DisplayAlerts = False ActiveWorkbook.SaveCopyAs sName & "AA.xls" Application.DisplayAlerts = True v = "#Work Order##Packing Slip##Invoice##Release#" _ & "#Shipping##Master Price List#" Set bk = Workbooks.Open SName & "AA.xls" for each sh in bk.Worksheets if instr(1,v,"#" & sh.Name & "#",vbTextCompare) = 0 then Application.DisplayAlerts = False sh.delete application.DisplayAlerts = True end if Next -- Regards, Tom Ogilvy "Gixxer_J_97" wrote in message ... hi all thanks to everyone for their help so far! i have a vba project in excel that saves several sheets to a new workbook. thus far i was saving only the values using <BEGIN VBA CODE Worksheets(Array("Work Order", "Packing Slip", "Invoice", "Release", "Shipping", "Master Price List")).Copy Set wkbk = ActiveWorkbook For Each sh In wkbk.Worksheets With sh.UsedRange .Value = .Value End With Next <END VBA CODE almost everything seemed to work perfectly, however some cells in the saved (new) workbook will have #Name? instead of the value of the cell of the parent worksheet. the cells that generate the #Name? after being copied have the following formula in the master sheet cell <BEGIN EXCEL FORMULA =IF(ISBLANK(C27),"",chooselotnumber(VLOOKUP(C27,'M aster Price List'!$B$7:$O$44,10,FALSE))) <END EXCEL FORMULA the chooselotnumber function checks to see if there are multiple lot numbers for the choosen product. if there is only one, it returns that one. o/w it will prompt the user for which lot number to use. i would like to change my code to copy the formulas instead of just the values. (the new workbook will need to be opened and updated, so having the formulas there is a great help) also, i have a function in the master workbook that is used in some of the calculations. how do i modify the above code to copy the formulas, ( .FormulaR1C1 = .FormulaR1C1 ?) and also copy the function i use to the new workbook? thanks! J |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't copy formulas in a worksheet | Excel Discussion (Misc queries) | |||
how do I copy a worksheet with formulas to another workbook? | Excel Worksheet Functions | |||
Copy Worksheet With Formulas | Excel Discussion (Misc queries) | |||
copy formulas to different cells in another worksheet | Excel Discussion (Misc queries) | |||
Range COPY function - how to copy VALUES and not formulas | Excel Programming |