Copy selected sheet and VBA modules to a new workbook

[aashish83]

I have several macros in a workbook and the function of one of the macro is to create a new workbook via the the macro.....the problem is that when in the new workbook i have to run the other macros it is only possible if the original sheet is open or saved on the system locally if not the macros on the new work book gives error?

for e.g.

workbook1 is named A which has 5 sheets and has 17 different macros in it and function of macro 3 is to create a new workbook named B with all the data per sheet 2 from work book A. now if i run the macro 7 in workbook B and if workbook A is not saved on my system it gives an error. i presume it is because the original macro is placed in workbook A .

is it possible to have the macro also copied to workbook B so that it can run individually without dependency on workbook A.

Code for Macro where a new workbook B is created from sheets in workbook A

Sub FSRA()

Dim rng As Range
With ActiveWorkbook.Sheets(Array("FSR Level A", "BI", "Instructions"))
Set rng = ActiveWorkbook.Worksheets("BI").Range("A1:B28")
.Copy
ActiveWorkbook.Worksheets("BI").Range("B29:B31").EntireRow.Hidden = True
ActiveWorkbook.Worksheets("BI").Range("A1:B28") = rng.Value2
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & "Audit Form FSR A", FileFormat:=52
End With

End Sub

Code for Macro which gives an error when workbook A is not saved on the local system

Sub Yes_to_All_A()

ActiveSheet.Range("$A$32:$E$261").AutoFilter Field:=5, Operator:= _
xlFilterNoFill
Range("E34").Select
ActiveCell.FormulaR1C1 = "Yes"
Range("E34").Select
Selection.Copy
Range("E34:E261").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$A$33:$E$261").AutoFilter Field:=5
Range("H23").Select

End Sub

 

[RoryA]

This sounds like a bad setup to me, but the simplest way is probably just to save the workbook as a new name, then delete any sheets you don't want in that copy.

 

[aashish83]

This sounds like a bad setup to me, but the simplest way is probably just to save the workbook as a new name, then delete any sheets you don't want in that copy.

i am afraid it has to be automated.

 

[RoryA]

I wasn't suggesting you do it manually!

 

[aashish83]

I wasn't suggesting you do it manually!

Thanks Rory did not think of it as a possibility till you suggested it and it worked out fine