What exactly do you want/not want in the new 'PO' workbook(s)?
What exactly do you want/not want in the new 'PO' workbook(s)?
If posting code please use code tags, see here.
so i made a change to the code, it's failing on Workbooks("Purchase order-Template").Sheets("Formulas").Copy Before:=openworkbook.Sheets(1), gives me a run time error '91', object variable or with block variable not set
thoughts?Sub CreatePO() 'copy sheet to new workbook Dim wb As Workbook abc = ActiveSheet.Name Sheets("formulas").Range("AB1") = Sheets(abc).Range("I39") Set wb = Workbooks.Add ThisWorkbook.ActiveSheet.Copy Before:=wb.Sheets(1) 'save workbook as xlsm 'wb.SaveAs ActiveSheet.Name, FileFormat:=52 wb.SaveAs ThisWorkbook.Path & "\" & abc, FileFormat:=52 Dim openworkbook Dim x1 Dim FNametxt As String, FNamefrm As String, FileSelected As String, FileName As String With ThisWorkbook Let FNametxt = .Path & "\PO\code.txt" .VBProject.VBComponents("Module1").Export FNametxt Let FNamefrm = .Path & "\PO\form.frm" .VBProject.VBComponents("UserForm1").Export FNamefrm Let FNamecal = .Path & "\PO\calendar.frm" .VBProject.VBComponents("frmCalendar").Export FNamecal End With Workbooks.Open abc Sheets(abc).Activate ActiveWorkbook.VBProject.VBComponents.Import FNametxt ActiveWorkbook.VBProject.VBComponents.Import FNamefrm ActiveWorkbook.VBProject.VBComponents.Import FNamecal ActiveWorkbook.Save Set openworkbook = Workbooks.Open(abc) Application.ScreenUpdating = False 'With Workbooks("Purchase order-Template") Workbooks("Purchase order-Template").Activate Workbooks("Purchase order-Template").Sheets("Formulas").Copy Before:=openworkbook.Sheets(1) Workbooks("Purchase order-Template").Sheets("Master").Copy Before:=openworkbook.Sheets(1) Workbooks("Purchase order-Template").Sheets("BigMaster").Copy Before:=openworkbook.Sheets(1) Workbooks("Purchase order-Template").Sheets("Estimating1").Copy Before:=openworkbook.Sheets(1) Workbooks("Purchase order-Template").Activate 'End With 'close purchase order-template, DONT SAVE Workbooks("Purchase Order-template.xlsm").Activate 'Application.DisplayAlerts = False 'ActiveWorkbook.Close 'Application.DisplayAlerts = True 'another way to close the file without seeing any prompts Workbooks("Purchase Order-template.xlsm").Close SaveChanges:=False End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks