Extracting specific data from a workbook with different no_sheet to another workbook

[RAM72]

Hi All

Need great help. recieving workbook named master file with different order numbers sheets up to 50 numerical sheets (see annexed )

For my concern, I need to extract only data from header 1 to header 7 and to paste it on processing sheet workbook (see annexed).
On processing sheet I need to insert a column A named Reference sheet manually then I had put and drag according to number of rows that is supposse sheet 11111 has 80 rows of data , i need to copy from HEADER 1 TO TO HEADER 7 these data up to to put up to rows 80 where data stops and sheet 22222 if there 120 rows of data and same process goes on until finished to end .Most masterwork books has maximum 50 numerical data sheet which differ in the no of rows of data required .

Thus I am looking for a formula , macro or VBA which can solve my daily painful task as this is not easy.

thanks to those who can help me

 

[Deepak]

Ok.Stay tuned. Will back soon!!

 

[Deepak]

Check this...

Sub Process_Data()
Dim ws As Worksheet, myWB As Workbook, rCount As Integer, r As Integer

Application.ScreenUpdating = False
rCount = 2
Set myWB = Workbooks.Add
[A1:H1] = Array("REFRENCE SHEET", "HEADER_1", "HEADER_2", "HEADER_3", "HEADER_4", "HEADER_5", "HEADER_6", "HEADER_7")
  
    For Each ws In ThisWorkbook.Worksheets
        If IsNumeric(ws.Name) Then
            ws.Range("A1").CurrentRegion.Offset(1).Resize(, 7).Copy
            myWB.Sheets(1).Cells(rCount, 2).PasteSpecial xlPasteValues
          
            r = rCount + ws.Range("A1").CurrentRegion.Rows.Count - 2
          
            myWB.Sheets(1).Range(Cells(rCount, 1), Cells(r, 1)).Value = ws.Name
            rCount = rCount + ws.Range("A1").CurrentRegion.Rows.Count - 1
        End If
    Next
ActiveSheet.Columns.AutoFit
myWB.SaveAs Application.ThisWorkbook.Path & "PROCESSING SHEET_Data.xlsx"
Application.ScreenUpdating = True
End Sub