VBA code to copy all data not only one row data

[angelmaine4ever]

Hi, pls help.

I am combining all worksheets data into one worksheet. Below is the code.
However, it's only copying the second row data (below header).
How can I copy all?

Thanks!

Sub Button2_Click()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "READ ME" And Sht.Name <> "PRIDE Ratings' Distribution" And Sht.Name <> "Computation" Then
Sht.Select
Range("A:A").Insert
Range("A2").Formula = "=Mid(Cell(""filename"",B1),Find(""]"",Cell(""filename""))+1,255)"
Range("A2").Copy
Range("A2").PasteSpecial Paste:=xlPasteValues
Range("A2:J2").Copy
Sheets("Master").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sht.Select
Range("A:A").Delete
Else
End If
Next Sht
End Sub

 

[My Aswer Is This]

Your code wants to not exclude some sheets. So did you write this script and understand it?

You said:
I am combining all worksheets data into one worksheet

It seems like you want to delete column A in your sheet name "Master"
Is that what you want to do?

 

[mrshl9898]

Like this?

Sub Button2_Click()Dim Sht As Worksheet
Dim Lastrow As Long


For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "READ ME" And Sht.Name <> "PRIDE Ratings' Distribution" And Sht.Name <> "Computation" Then
Sht.Select
Range("A:A").Insert
Range("A2").Formula = "=Mid(Cell(""filename"",B1),Find(""]"",Cell(""filename""))+1,255)"
Range("A2").Copy
Range("A2").PasteSpecial Paste:=xlPasteValues
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:J" & Lastrow).Copy
Sheets("Master").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sht.Select
Range("A:A").Delete
Else
End If
Next Sht
End Sub

 

[Rick Rothstein]

I think this macro will do what you want (run it with the Master sheet active)...

[table="width: 500"]
[tr]
	[td]Sub Button2_Click()
  Dim NextRow As Long, Sht As Worksheet
  For Each Sht In Worksheets
    If Sht.Name <> "Master" And Sht.Name <> "READ ME" And Sht.Name <> "PRIDE Ratings' Distribution" And Sht.Name <> "Computation" Then
      NextRow = Sheets("Master").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
      Sheets("Master").Cells(NextRow, "A").Value = Sht.Name
      Sheets("Master").Cells(NextRow, "B").Resize(Sht.UsedRange.Rows.Count, 9) = Sht.UsedRange.Value
    End If
  Next
End Sub[/td]
[/tr]
[/table]

 

[angelmaine4ever]

Your code wants to not exclude some sheets. So did you write this script and understand it?

You said:
I am combining all worksheets data into one worksheet

It seems like you want to delete column A in your sheet name "Master"
Is that what you want to do?

Hi. I am new to VBA. My friend's friend helped me create this code. So, nope. I do not want to delete it.
Would you know the code that will combine all data from multiple worksheets into one?
But exclude/skip READ ME tab? Thanks!

 

[angelmaine4ever]

I think this macro will do what you want (run it with the Master sheet active)...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Button2_Click()
  Dim NextRow As Long, Sht As Worksheet
  For Each Sht In Worksheets
    If Sht.Name <> "Master" And Sht.Name <> "READ ME" And Sht.Name <> "PRIDE Ratings' Distribution" And Sht.Name <> "Computation" Then
      NextRow = Sheets("Master").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
      Sheets("Master").Cells(NextRow, "A").Value = Sht.Name
      Sheets("Master").Cells(NextRow, "B").Resize(Sht.UsedRange.Rows.Count, 9) = Sht.UsedRange.Value
    End If
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Hi Sir, thanks! Almost there! I just have a problem that it's also copying the first row (headers). How do I exclude them?

 

[Rick Rothstein]

Hi Sir, thanks! Almost there! I just have a problem that it's also copying the first row (headers). How do I exclude them?

See if this works for you...

[table="width: 500"]
[tr]
	[td]Sub Button2_Click()
  Dim NextRow As Long, Sht As Worksheet
  For Each Sht In Worksheets
    If Sht.Name <> "Master" And Sht.Name <> "READ ME" And Sht.Name <> "PRIDE Ratings' Distribution" And Sht.Name <> "Computation" Then
      NextRow = Sheets("Master").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
      Sheets("Master").Cells(NextRow, "A").Value = Sht.Name
      Sheets("Master").Cells(NextRow, "B").Resize(Sht.UsedRange.Rows.Count, 9) = Sht.UsedRange.Offset(1).Value
    End If
  Next
End Sub[/td]
[/tr]
[/table]

 

[angelmaine4ever]

See if this works for you...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Button2_Click()
  Dim NextRow As Long, Sht As Worksheet
  For Each Sht In Worksheets
    If Sht.Name <> "Master" And Sht.Name <> "READ ME" And Sht.Name <> "PRIDE Ratings' Distribution" And Sht.Name <> "Computation" Then
      NextRow = Sheets("Master").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
      Sheets("Master").Cells(NextRow, "A").Value = Sht.Name
      Sheets("Master").Cells(NextRow, "B").Resize(Sht.UsedRange.Rows.Count, 9) = Sht.UsedRange.Offset(1).Value
    End If
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thanks so much!!! It worked!!! I am so new to this. But I really need to finish my excel.

I would like to understand how to exclude sheet with no data too. So sorry if I have lots of questions.