Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open mutiple spreadsheets in one session of Excel and use 'ALT' + | Setting up and Configuration of Excel | |||
Running mutiple excel sheets on mutiple computers | Excel Discussion (Misc queries) | |||
Employees & Managers | Excel Discussion (Misc queries) | |||
How to use mutiple spreadsheets in a pivot table? | Excel Discussion (Misc queries) | |||
creating mutiple worksheets | Excel Discussion (Misc queries) |