Need help with Excel dynamic report

[userx69]

HI everybody I need to build up one dynamic report.

Here is the task:
1) Everyday workers fill up with records in each sheet (record count can be different, because there is a "List" table)
2) Each day is in one separate sheet (for example: 1st June is in sheet with name 1, 2nd June is in sheet with name 2 .)
3) ALL sheets are named 1, 2, 3 ... 31
4) And in the sheet with name "ALL records" must be generated all records from sheet 1,2, ... 31

Here is my Example

Could you me please?

 

[sanrv1f]

Hi and welcome to the board,

try this

Sub Copy_to_Master()
Dim sh1 As Integer, lastrow As Long, ws As Worksheet
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
sh1 = Worksheets("All Records").Index
For Each ws In Worksheets
lastrow = Worksheets(sh1).UsedRange.Rows.Count
If ws.Index <> sh1 Then
    ws.Activate
    ws.ListObjects(1).DataBodyRange.Copy
    Worksheets(sh1).Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End If
Next ws
Worksheets(sh1).Activate
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[userx69]

Thank you sanrv1f! It works perfectly!

 

[sanrv1f]

your PM

[FONT=&quot]Thank You for Your answer.[/FONT]
[FONT=&quot]But now I have to put a couple of sheets and I don't want to be included in your this macro (for example "Sheet1" and "Sheet2" macro must not include in its process). What I should put in your code? Help me again, please. [/FONT]
Here is my example

try

Sub Copy_to_Master()
Dim sh1 As Integer, lastrow As Long, ws As Worksheet
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
sh1 = Worksheets("All Records").Index
For Each ws In Worksheets
lastrow = Worksheets(sh1).UsedRange.Rows.Count
If ws.Index <> sh1 and ws.name<>"Sheet1" and ws.name<>"Sheet2" Then
    ws.Activate
    ws.ListObjects(1).DataBodyRange.Copy
    Worksheets(sh1).Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End If
Next ws
Worksheets(sh1).Activate
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[userx69]

Many thank you again!!

 

[maheshmaxi]

Hi and welcome to the board,

try this

Sub Copy_to_Master()
Dim sh1 As Integer, lastrow As Long, ws As Worksheet
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
sh1 = Worksheets("All Records").Index
For Each ws In Worksheets
lastrow = Worksheets(sh1).UsedRange.Rows.Count
If ws.Index <> sh1 Then
    ws.Activate
    ws.ListObjects(1).DataBodyRange.Copy
    Worksheets(sh1).Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End If
Next ws
Worksheets(sh1).Activate
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Thanks sanrv1f. If I follow this code, should I rename my sheets? pls advise...

 

[sanrv1f]

you need not rename your sheet, just change the below line to match your master sheet name

sh1 = Worksheets("All Records").Index

 

[maheshmaxi]

I have renamed my sheet as "All Records". But how to use this code. Can I paste it in new module. Pls advise, as I am not familiar with VBA.

 

[sanrv1f]

ok, this thread had a specifc requirement involving lists, and I believe below link would help,

http://www.mrexcel.com/forum/showthread.php?t=403594

copy the code t oa new module and change the sheet name in the code, then run the macro

 

[maheshmaxi]

Thanks sanrv1f. It works fine