copy worksheet with formulas and vba function

[Gixxer_J_97]

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,'Master 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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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,'Master 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

[Gixxer_J_97]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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,'Master 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
>
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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,'Master 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
> >
> >
> >