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!

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!