Error while combining multiple sheets into Master Sheet with VBA [SOLVED]

[Pooja]

Hi,

I am trying to combine data from multiple sheets into Master sheet in same workbook.
I have written the code but it is working on for few sheets and gives error and stopped.
The data structure for all the sheets is similar. I have attached the same excel file.
Please help me resolve this problem.

 

[Hui]

I made two small changes to the If line

Sub CombineData()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
  If Sht.Name <> "Master" And Sht.Visible = -1 And Sht.Range("A2").Text <> "" Then
  Sht.Select
  LastRow = Range("A65536").End(xlUp).Row
  Range("A2", Cells(LastRow, "G")).Copy
  Sheets("Master").Select
  Range("A65536").End(xlUp).Offset(1, 0).Select
  ActiveSheet.Paste
  End If
Next Sht

End Sub

Sht.Visible = -1 ensures that only Visible sheets are copied, You have 1 Hidden sheet, Sheet 7
Sht.Range("A2").Text <> "" Check the text, not the value

 

[Pooja]

Hey Hui,

Thanks a ton, this is working fine now.

 

[Pooja]

Hi Hui,

I need to make this code more user friendly. Is it possible to copy data only from specific/specified sheets ? So that we don't have to hide sheet which need not to be copied. If we do a table with sheet names to be copied by macro. Or any other solution. Kindly help on this.

 

[Hui]

Try something like

Sub CombineData()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
  If Sht.Name = Array("Sheet01", "Sheet02", "Sheet03") and Sht.Visible = -1 And Sht.Range("A2").Text <> "" Then
  Sht.Select
  LastRow = Range("A65536").End(xlUp).Row
  Range("A2", Cells(LastRow, "G")).Copy
  Sheets("Master").Select
  Range("A65536").End(xlUp).Offset(1, 0).Select
  ActiveSheet.Paste
  End If
Next Sht

End Sub

 

[Pooja]

Hi Hui,

I am getting Run time error "13", Type mismatch in the below line;

If Sht.Name = Array("Sheet01", "Sheet02", "Sheet03") and Sht.Visible = -1 And Sht.Range("A2").Text <> ""Then

 

[Hui]

You can always do:

If (Sht.Name = "Sheet01" or Sht.Name = "Sheet02" or Sht.Name = "Sheet03") and Sht.Visible = -1 And Sht.Range("A2").Text <> ""Then

 

[rumshar]

Hi,
Please use this add in.
http://www.rondebruin.nl/win/addins/rdbmerge.htm

 

[Pooja]

Hi Hui,
This is working good now as per required. Now I have another problem.
I have done conditional formatting in column "D&E" this is disappeared when I run the macro to refresh the data.
How can we use macro without affecting the formatting ?
If you need I can attach the file.