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
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