Creating spreadsheets for managers with mutiple tabs
 

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!

Creating spreadsheets for managers with mutiple tabs
 

Anyone...?

Thanks...!

"Nico" wrote:

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!