E
EMarre
I have recorded the Macro (please see below) in a formatted Profit and Loss
budget worksheet. The worksheet uses columns and rows Show/Hide Groups to
extract data from the worksheet using the 'Copy' Visible Cells only command
into another worksheet within the same workbook. I had done this to eliminate
totals, formats, etc. and be able to extract only Account and Projections
data entered by users into the worksheets. This data will be imported into a
budget system later on using a text format file. The issue I am having is
that one workbook could contain hundreds of departments P&L budget in
separate tabs or worksheets and I don't know how to loop through all the
worksheets using the same macro to extract the data using the referenced
macro.
Any help that could be provided on this issue will be really appreciated.
Thanks in advance,
Please see macro below:
Sub Data_Extract()
'
' Keyboard Shortcut: Ctrl+e
'
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
ActiveSheet.Outline.ShowLevels RowLevels:=1
Range("D20:AB700").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Extracted Data for Import").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B2").Select
Sheets("Report").Select
Range("Z20").Select
Application.CutCopyMode = False
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2
ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub
budget worksheet. The worksheet uses columns and rows Show/Hide Groups to
extract data from the worksheet using the 'Copy' Visible Cells only command
into another worksheet within the same workbook. I had done this to eliminate
totals, formats, etc. and be able to extract only Account and Projections
data entered by users into the worksheets. This data will be imported into a
budget system later on using a text format file. The issue I am having is
that one workbook could contain hundreds of departments P&L budget in
separate tabs or worksheets and I don't know how to loop through all the
worksheets using the same macro to extract the data using the referenced
macro.
Any help that could be provided on this issue will be really appreciated.
Thanks in advance,
Please see macro below:
Sub Data_Extract()
'
' Keyboard Shortcut: Ctrl+e
'
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
ActiveSheet.Outline.ShowLevels RowLevels:=1
Range("D20:AB700").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Extracted Data for Import").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B2").Select
Sheets("Report").Select
Range("Z20").Select
Application.CutCopyMode = False
ActiveSheet.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2
ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub