Macro that extracts info from changing number of worksheets
Closed
alita1984
Posts
1
Registration date
Monday November 20, 2017
Status
Member
Last seen
November 20, 2017
-
Nov 20, 2017 at 02:48 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Nov 21, 2017 at 10:36 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Nov 21, 2017 at 10:36 PM
Related:
- Macro that extracts info from changing number of worksheets
- Spell number in excel without macro - Guide
- Changing phone number t mobile - Guide
- How to change number of best friends on snapchat - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- Automatically run a macro when opening a worksheet - Guide
2 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Nov 20, 2017 at 09:53 PM
Nov 20, 2017 at 09:53 PM
Hello Alex,
Try the following code placed in a standard module and assigned to a button:-
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.
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.
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Nov 21, 2017 at 10:36 PM
Nov 21, 2017 at 10:36 PM
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:-
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.
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.
Nov 21, 2017 at 06:29 PM
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!