Macro that extracts info from changing number of worksheets [Closed]

Posts
1
Registration date
Monday November 20, 2017
Status
Member
Last seen
November 20, 2017
- - Latest reply: vcoolio
Posts
1248
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 17, 2019
- Nov 21, 2017 at 10:36 PM
Hello,


I'm trying to find a way to insert a summary of several worksheets (I'm calling them analysis logs), but want to make it in such a way that if the number of worksheets change, or if I insert a worksheet from another file, the summary page will recognize this and add all the analysis logs.

Is this possible with a Macro?

Thanks
Alex


See more 

2 replies

Posts
1248
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 17, 2019
205
0
Thank you
Hello Alex,

Try the following code placed in a standard module and assigned to a button:-


Sub Consolidate()

Dim ws As Worksheet

Application.ScreenUpdating = False

Sheet1.UsedRange.Offset(1).ClearContents

For Each ws In Worksheets
     If ws.Name <> "Summary" Then
         ws.UsedRange.Offset(1).Copy
             Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
       End If
Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


I'm assuming that you would like all the data from each sheet consolidated into the Summary sheet (Sheet1).

The above code will do this regardless of the number of sheets in your workbook. The code will first clear any existing data in the Summary sheet prior to any new data being transferred over.

I hope that this helps.

Cheerio,
vcoolio.
Hi vcoolio,

Thanks for this. I think I'm getting closer. But I actually only want to extract a define range from each sheet, and add all the info from these ranges into my summary sheet.

I've been reading that I need to program a dynamic array, but can't get it to work.

Any ideas?

Thanks again!
Posts
1248
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 17, 2019
205
0
Thank you
Hello Alex,

I don't think you need to worry about an array. If you have a defined, named range then adjusting the above code as follows may be all that you need:-

Sub Consolidate()

Dim ws As Worksheet

Application.ScreenUpdating = False

Sheet1.UsedRange.Offset(1).ClearContents

For Each ws In Worksheets
     If ws.Name <> "Summary" Then
         ws.Range("XXXXX").Copy
             Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
       End If
Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


In line 11, where I have placed "XXXXX", place the name of your defined range then test the code to see if it works.

If it still doesn't quite work, upload a sample of your workbook to a free file sharing site such as ge.tt or Drop Box then post the link to your file back here. It will be easier to sort this out for you if we have something to test with. Please use dummy data in your sample.

Cheerio,
vcoolio.