Hi,
I need help to combine worksheet named "Metal List" from different Workbooks who have many worksheets and the worksheet name "Metal List",into one sheet to get a consolidated data and I want it in a new workbook. I want the 1st row of all the merging sheets to be ignored as they contain the headings.
I am using the below code but its not being very useful as it seems to copy the data of the first sheet twice!! and does not ignore the first row so there are multiple heading in the sheet.
{If possible can it also automatically combine the worksheet "metal List" of workbooks saved in that particular folder or drive?}
Public Sub Merge()
Gato "merged.xlsm", "Metal List" ' use the names of the destination workbook and worksheet
End Sub
Private Sub Gato(BName As String, SName As String) ' combine data from multiple book/sheets into a single book/sheet
Dim wb As Workbook
Dim XX As String
Workbooks(BName).Sheets(SName).Activate
For Each wb In Workbooks
XX = Format(Str(URow(BName, SName)))
If wb.Name <> BName Then
If URow(BName, SName) + (URow(wb.Name, SName) - 1) < Workbooks(BName).Sheets(SName).Rows.Count Then
wb.Sheets(SName).Rows("1:" & URow(wb.Name, SName) - 1).EntireRow.Copy Workbooks(BName).Sheets(SName).Rows(XX)
Else
MsgBox ("contents of " & wb.Name & SName & Chr(13) & Chr(10) & _
"exceeds destination sheet capacity.")
End If
End If
Next wb
End Sub
Private Function URow(Book_Name As String, Sheet_Name As String) As Long
If (Workbooks(Book_Name).Worksheets(Sheet_Name).UsedRange.Rows.Count = 1 And _
Workbooks(Book_Name).Worksheets(Sheet_Name).UsedRange.Columns.Count = 1 And _
Workbooks(Book_Name).Worksheets(Sheet_Name).Cells(1, 1).Value = "") Then
URow = 1
Else
URow = Workbooks(Book_Name).Worksheets(Sheet_Name).Cells.Find(What:="*", After:=Cells(1, 1), _
LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
End If
End Function
Many Thanks for your help
Bookmarks