Macro that extracts info from changing number of worksheets
Closed
alita1984
Posts
1
Registration date
Monday 20 November 2017
Status
Member
Last seen
20 November 2017
-
20 Nov 2017 à 14:48
vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Contributor Last seen 6 September 2024 - 21 Nov 2017 à 22:36
vcoolio Posts 1411 Registration date Thursday 24 July 2014 Status Contributor Last seen 6 September 2024 - 21 Nov 2017 à 22:36
Related:
- Macro that extracts info from changing number of worksheets
- Change tmobile phone number from app - Guide
- Battery info view - Download - Diagnosis and monitoring
- How to split excel sheet into multiple worksheets - Guide
- How to change number of best friends on snapchat - Guide
- Changing keyboard language shortcut - Guide
2 responses
vcoolio
Posts
1411
Registration date
Thursday 24 July 2014
Status
Contributor
Last seen
6 September 2024
262
20 Nov 2017 à 21:53
20 Nov 2017 à 21:53
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
1411
Registration date
Thursday 24 July 2014
Status
Contributor
Last seen
6 September 2024
262
21 Nov 2017 à 22:36
21 Nov 2017 à 22:36
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.
21 Nov 2017 à 18:29
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!