N
Nico
Hello,
I'm modifying a macro created by someone else years before, and I'm trying
to clean up a process, but I'm experiencing some difficulty getting it to
work right.
At the moment the macro looks at a list of managers and applications in one
spreadsheet, filters for each manager and pastes the results into a template
spreadsheet which it saves with the manager's name. Though I'm sure there's
an easier way to do this, this part works fine.
When the person created the macro this reporting only covered about 4
applications, but today we're looking at 30+ for each manager (each
application in its own tab) and the macro is crashing halfway through.
If they don't have any users on a given application, I don't want a new tab
created (keeps the file size down, and reduces unnecessary clicking on their
part).
Below is the end part of the code, where it's crashing (Master = the
spreadsheet with all applications and all managers):
Windows(Master).Activate
For Applc = 1 To Sheets.Count
AttReq = "Yes"
Windows(Master).Activate
Sheets(Applc).Select
SheetName = ActiveSheet.Name
Selection.AutoFilter Field:=1, Criteria1:=SPOC
Cells.Select
Selection.Copy
Windows(SPOCFile).Activate
Sheets(SheetName).Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells.EntireColumn.AutoFit
Range("A1").Select
If IsEmpty(Cells(2, 1).Value) Then
Cells.Select
Selection.Clear
End If
Is there a way to clear the paste, or a better way to do this?
Thanks for any help you may be able to offer!
I'm modifying a macro created by someone else years before, and I'm trying
to clean up a process, but I'm experiencing some difficulty getting it to
work right.
At the moment the macro looks at a list of managers and applications in one
spreadsheet, filters for each manager and pastes the results into a template
spreadsheet which it saves with the manager's name. Though I'm sure there's
an easier way to do this, this part works fine.
When the person created the macro this reporting only covered about 4
applications, but today we're looking at 30+ for each manager (each
application in its own tab) and the macro is crashing halfway through.
If they don't have any users on a given application, I don't want a new tab
created (keeps the file size down, and reduces unnecessary clicking on their
part).
Below is the end part of the code, where it's crashing (Master = the
spreadsheet with all applications and all managers):
Windows(Master).Activate
For Applc = 1 To Sheets.Count
AttReq = "Yes"
Windows(Master).Activate
Sheets(Applc).Select
SheetName = ActiveSheet.Name
Selection.AutoFilter Field:=1, Criteria1:=SPOC
Cells.Select
Selection.Copy
Windows(SPOCFile).Activate
Sheets(SheetName).Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells.EntireColumn.AutoFit
Range("A1").Select
If IsEmpty(Cells(2, 1).Value) Then
Cells.Select
Selection.Clear
End If
Is there a way to clear the paste, or a better way to do this?
Thanks for any help you may be able to offer!