Select sheets in workbook then consolidate data

[Manish Sharma]

Hi friends,

* I have a workbook, with different sheets (with sheet name as Home, RawData, Data1, Data2, Data3, ...), these sheets are not fixed
* FYI... sheet (home) has a VBA code which on running creates a new sheet and pull data from some other source (So the sheet number is not fixed in the workbook, but the newly added sheet is named in a specific format as Data1, Data2, Data3, ...)
* So we can say, that the sheet number and name are not fixed (It keeps on changing)
* All these sheets have data in the same format starting from range A2:J2, except sheet(home)

Two things i am trying to do:-
- Select sheets from the workbook (to be consolidated)
- Consolidate selected sheets in a new sheet

Sample file is attached

Thanks & regards,
Manish

 

[Smallman]

Manish

Perhaps something like the following;

Option Explicit
Sub GetData()
Dim ws As Worksheet
Dim sh As Worksheet

Set sh = Worksheets.Add
sh.Name = "NewSheet"

For Each ws In Sheets
    If Left(ws.Name, 4) = "Data" Then
        ws.[a1].CurrentRegion.Offset(1).Copy sh.Range("A" & Rows.Count).End(xlUp)(2)
    End If
Next ws
End Sub

Take care

Smallman

 

[Manish Sharma]

Manish

Perhaps something like the following;

Option Explicit
Sub GetData()
Dim ws As Worksheet
Dim sh As Worksheet

Set sh = Worksheets.Add
sh.Name = "NewSheet"

For Each ws In Sheets
    If Left(ws.Name, 4) = "Data" Then
        ws.[a1].CurrentRegion.Offset(1).Copy sh.Range("A" & Rows.Count).End(xlUp)(2)
    End If
Next ws
End Sub

Take care

Smallman

Thank Smallman, for replying
But, only partially my problem is solved.

I want to first choose the sheets then consolidate the data by adding a new sheet in the same workbook.

Like may be the first user will select only sheets (Data1, Data3 & Data4) and then he will consolidate the data
then second user may select Sheets (Data3, Data4 & Data5) and then he will consolidate the data

FYI... sheet (home) has a VBA code which on running creates a new sheet and pull data from some other source (So the sheet number is not fixed in the workbook, but the newly added sheet is named in a specific format as Data1, Data2, Data3, ...)

 

[Smallman]

This is a little bit trickier. I did not grasp your problem the first time around. It is an interesting one though because if your users select a group of sheets then you add a sheet your selection is lost. What you will need to do is trap your sheet names in a variable then only upload data from sheets which meet that variable name.

Option Explicit

Sub GetData()
Dim ws As Worksheet
Dim ar As Variant
Dim i As Integer
Dim j As Integer

j = ActiveWindow.SelectedSheets.Count
ReDim ar(1 To j)

For Each ws In Windows(1).SelectedSheets
    i = i + 1
    ar(i) = ws.Name
Next ws

Sheets("Home").Activate 'Required to prevent more than one sheet being added
Worksheets.Add
ActiveSheet.Name = "New"

For i = 1 To j
  Sheets(ar(i)).[a1].CurrentRegion.Offset(1).Copy Sheets("New").Range("A" & Rows.Count).End(xlUp)(2)
Next i

End Sub

I think that should do it.

Take care

Smallman

 

[Debraj]

@Manish Sharma

I would suggest to choose any other option for sheet selection..
May be List with checkbox, or multiselect Listbox, or even Text Input from user..
working with Select & WithSelection, may cause untrappable error.

Sub ConsolidateSelected()
Set sh = Worksheets.Add
sh.Name = "NewSheet"
shtext = InputBox("Enter sheet(s) name in s1,s4,s5 format", , "Data1,Data5,data3")
shtarr = Split(shtext, ",")
For i = 0 To UBound(shtarr)
  Sheets(shtarr(i)).[a1].CurrentRegion.Offset(1).Copy sh.Range("A" & Rows.Count).End(xlUp)(2)
Next
End Sub

 

[Smallman]

That is good Deb - real good. It did not work on my machine without the variables. So I put them in and it worked a treat.

Option Explicit
Sub ConsolidateSelected()
Dim sh As Worksheet
Dim shtext As String
Dim shtarr As Variant
Dim i As Integer

Set sh = Worksheets.Add
sh.Name = "NewSheet"
shtext = InputBox("Enter sheet(s) name in s1,s4,s5 format", , "Data1,Data5,data3")
shtarr = Split(shtext, ",")

For i = 0 To UBound(shtarr)
  Sheets(shtarr(i)).[a1].CurrentRegion.Offset(1).Copy sh.Range("A" & Rows.Count).End(xlUp)(2)
Next
End Sub

I like the idea of having text already in the text box, saves extra typing.

Smallman

 

[Manish Sharma]

This is a little bit trickier. I did not grasp your problem the first time around. It is an interesting one though because if your users select a group of sheets then you add a sheet your selection is lost. What you will need to do is trap your sheet names in a variable then only upload data from sheets which meet that variable name.

Option Explicit

Sub GetData()
Dim ws As Worksheet
Dim ar As Variant
Dim i As Integer
Dim j As Integer

j = ActiveWindow.SelectedSheets.Count
ReDim ar(1 To j)

For Each ws In Windows(1).SelectedSheets
    i = i + 1
    ar(i) = ws.Name
Next ws

Sheets("Home").Activate 'Required to prevent more than one sheet being added
Worksheets.Add
ActiveSheet.Name = "New"

For i = 1 To j
  Sheets(ar(i)).[a1].CurrentRegion.Offset(1).Copy Sheets("New").Range("A" & Rows.Count).End(xlUp)(2)
Next i

End Sub

I think that should do it.

Take care

Smallman

Thanks Smallman, its working but only one problem is there. In the consolidated sheet header is not retained in row A1.

 

[Manish Sharma]

@Manish Sharma

I would suggest to choose any other option for sheet selection..
May be List with checkbox, or multiselect Listbox, or even Text Input from user..
working with Select & WithSelection, may cause untrappable error.

Sub ConsolidateSelected()
Set sh = Worksheets.Add
sh.Name = "NewSheet"
shtext = InputBox("Enter sheet(s) name in s1,s4,s5 format", , "Data1,Data5,data3")
shtarr = Split(shtext, ",")
For i = 0 To UBound(shtarr)
  Sheets(shtarr(i)).[a1].CurrentRegion.Offset(1).Copy sh.Range("A" & Rows.Count).End(xlUp)(2)
Next
End Sub

Wah!!!! Lovely exaclty how i was trying to do it. I am not able to believe that with such a small code this is possible. Thanks a lot Deb Ur really a genius

But there is only a small issue with the code, in the consolidated sheet, the header row is not retained, can we retain the header row in range A1

 

[Smallman]

If you are going to use the macro I suggested then just add something like this at the end of the code.

Sheets("New").[a1:J1].Value = Sheets("Data1").[a1:J1].Value

Take care

Smallman

 

[Debraj]

Smallman,
Thanks, I will take care next time.. defining veriable is a good and must-have practice..

 

[Manish Sharma]

Hi Smallman nad Debraj; can you guys please help me, in retaining header (only in first row) of consolidated data sheet in the code where InputBox is used?

 

[Manish Sharma]

If you are going to use the macro I suggested then just add something like this at the end of the code.

Sheets("New").[a1:J1].Value = Sheets("Data1").[a1:J1].Value

Take care

Smallman

Sorry Smallman, but i was talking about the code where you have used inputbox. Can we make sme changes in that code to retain the header of the consolidated data table?

 

[Manish Sharma]

Hi Smallman and Debraj, i made a small change in the code to retain header in the consolidated sheet's data. below is the code.

Sub ConsolidateSelected2()
Dim sh As Worksheet
Dim shtext As String
Dim shtarr As Variant
Dim i As Integer

Set sh = Worksheets.Add
sh.Name = InputBox("Give name.")

shtext = InputBox("Enter sheet(s) name in s1,s4,s5 format", , "Data1,Data5,data3")
shtarr = Split(shtext, ",")

For i = 0 To UBound(shtarr)
  Sheets(shtarr(i)).[a1].CurrentRegion.Copy sh.Range("A" & Rows.Count).End(xlUp)(2)
Next

sh.Range("A2").CurrentRegion.RemoveDuplicates (Array(1, 2, 3,4,5,6,7,8,9,10))
End Sub

Please let me know if it is the correct way or there is some other way to do it.

 

[Smallman]

Hi Manish

The code you have incorporated to remove the duplicates is OK. The code you quoted in post 12 (or post 9s code) where the values are equal to the headers on a page should be fine also as long as you have the sheet name correct. Perhaps you could use the sheet code name to get around this possible issue.

Sh.[a1:J1].Value = Sheet1.[a1:J1].Value

Where sheet1 is one of the ones named Data1 Data2 etc. This should work also.

Take care

Smallman

 

[Manish Sharma]

Thanks Smallman. for the suggestion. I will go as per your suggestion

Thanks and regards,
Manish