The attached sheet has my sample data - forum.ozgrid.com/index.php?attachment/65866/ .
I was able to build code to merge data in all sheets into a single sheet (all headers in all sheets are consistent). Data to be merged with headers starts on row7.
My aim is (and I am struggling here) is to run this code from a different workbook say "MergedSheets" which will
- Prompt the user to select the target file from which sheets are to be merged.
- Combine/merge all data sheets into one but this combined result should get added as a new worksheet in the "MergedSheets" worksheet with tab name as today's date.
- Everyday/everytime this macro is run a new worksheet with today's date as name should get populated alongside yesterday's sheet.
My current code is as below:
Sub ShtCombine() Dim ws As Worksheet 'Add Top Sheet or Pasting Combined Data Worksheets.Add().Name = "All_Sheets_Data" 'Add Headings in first Row of top Sheet Sheets(2).Cells(7, 1).EntireRow.Copy Sheets(1).Cells(1, 1).PasteSpecial 'Combine relevant rows to the mastersheet For Each ws In ActiveWorkbook.Worksheets If ws.Name <> ActiveSheet.Name Then ws.UsedRange.Offset(7).Copy With Range("A65536").End(xlUp).Offset(1, 0) .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False .PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Range("A1").Select End With End If Next End Sub