Data Extract from Multiple Worksheets Macro
 

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

Data Extract from Multiple Worksheets Macro
 

This is only a half a fix as I'm new to vb and not sure of all the code
possiblities...

do
your code
activesheet.next.select
loop

only problem with this is that you will get an error once your code is done
on the final tab/sheet as there isn't another sheet to move to. Sorry I
can't be of more help then that. Hopefully this will get you closer to a
solution.

"EMarre" wrote:

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

Data Extract from Multiple Worksheets Macro
 

Give this a try...

Sub Data_Extract()
Dim rngPaste As Range
Dim wksCopy As Worksheet
Dim wksPaste As Worksheet
Set wksPaste = Sheets("Extracted Data for Import")

For Each wksCopy In ThisWorkbook.Worksheets
If wksCopy.Name < wksPaste.Name Then
With wksCopy
.Outline.ShowLevels RowLevels:=0, ColumnLevels:=1
.Outline.ShowLevels RowLevels:=1
.Range("D20:AB700").SpecialCells(xlCellTypeVisible ).Copy
Set rngPaste = wksPaste.Cells(Rows.Count,
"B").End(xlUp).Offset(1, 0)
rngPaste.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
.Outline.ShowLevels RowLevels:=0, ColumnLevels:=2
.Outline.ShowLevels RowLevels:=2
End With
End If
Next wksCopy
End Sub

--
HTH...

Jim Thomlinson


"EMarre" wrote:

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

Data Extract from Multiple Worksheets Macro
 

Thanks so much for your reply and the tip. I really appreciate it and will
give it a try.

"Mr. m0le" wrote:

This is only a half a fix as I'm new to vb and not sure of all the code
possiblities...

do
your code
activesheet.next.select
loop

only problem with this is that you will get an error once your code is done
on the final tab/sheet as there isn't another sheet to move to. Sorry I
can't be of more help then that. Hopefully this will get you closer to a
solution.

"EMarre" wrote:

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