• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Pooja

New Member
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.
 

Attachments

  • EaaS_WorkPlan.xlsm
    58.4 KB · Views: 4
I made two small changes to the If line
Code:
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
 
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.
 
Try something like

Code:
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
 
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
 
You can always do:
Code:
If (Sht.Name = "Sheet01" or Sht.Name = "Sheet02" or Sht.Name = "Sheet03") and Sht.Visible = -1 And Sht.Range("A2").Text <> ""Then
 
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.
 
Back
Top