Sheets Data to be Displayed in a Worksheet

[arijitirf]

Hello!!
I have a workbook having more than 1700 sheets (Starts as Stock Code 1, Stock Code 2 ...) in which data starts from Column A8. I want to copy A8 to D (last row of Column D) to a new sheet with sheet name. In same way it will also display Column F9 to I (last row of Column I) and data will be looking like

Sheet Name	Date	MIGO	Unit	Qty.		Sheet Name	Issue Date	Req. No.	Unit	Qty.
Stock Code 1	24-11-2014	5001040239	No.	2		Stock Code 1	26-12-2015	96	No.	2
Stock Code 1	05-03-2016	5001543431	No.	1		Stock Code 2	26-12-2015	97	No.	6
Stock Code 2	26-11-2014	5001042671	No.	6		Stock Code 2	02-01-2019	GEN-20	No.	3
Stock Code 2	19-04-2016	5001591782	No.	3		Stock Code 3	08-12-2015	79	No.	3
Stock Code 2	22-01-2019	5002736188	No.	1578		Stock Code 3	26-12-2018	EL-109	No.	52
Stock Code 3	18-12-2014	5001067389	No.	3		Stock Code 3	18-01-2019	EL-145	No.	10
Stock Code 3	12-03-2016	5001551257	No.	2		Stock Code 3	21-01-2019	EL-146	No.	1
Stock Code 3	15-12-2018	5002688227	No.	418		Stock Code 3	04-02-2019	EL-162	No.	124
Stock Code 3	15-12-2018	5002688227	No.	218		Stock Code 3	04-02-2019	EL-163	No.	22
Stock Code 4	18-12-2014	5001067427	No.	2		Stock Code 3	20-02-2019	EL-203	No.	18
Stock Code 4	12-03-2016	5001551258	No.	1		Stock Code 3	22-02-2019	EL-206	No.	58
Stock Code 4	08-01-2019	5002720336	No.	459		Stock Code 4	26-12-2015	96	No.	2

<colgroup><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

here is the link of my workbook

https://www.dropbox.com/s/9ynx9bhjgxbemxx/abc.xlsx?dl=0

Thanks in advance..

 

[JLGWhiz]

Sub t()
Dim sh As Worksheet, rng1 As Range, rng2 As Range, cnt1 As Long, cnt2 As Long
    For Each sh In ThisWorkbook.Sheets
        Sheets.Add After:=Sheets(Sheets.Count)
        If sh.Name Like "Stock Code*" Then
            Set rng1 = sh.Range("A8", sh.Cells(Rows.Count, 1).End(xlUp)).Resize(, 4)
            Set rng2 = sh.Range("F9", sh.Cells(Rows.Count, "F").End(xlUp)).Resize(, 7)
            cnt1 = rng1.Rows.Count
            cnt2 = rng2.Rows.Count
            rng1.Copy ActiveSheet.Range("B2")
            ActiveSheet.Range("A2").Resize(cnt1) = sh.Name
            rng2.Copy ActiveSheet.Cells(Rows.Count, 2).End(xlUp)(2)
            ActiveSheet.Cells(Rows.Count, 1).End(xlUp)(2).Resize(cnt2) = sh.Name
        End If
    Next
End Sub

 

[arijitirf]

Thanks for your prompt reply. Your code is pulling sheet data in different sheets (need to pull data from different sheets to a single sheet namely "Master"). Also it couldn't manage to pull last row data.

Please extend your support and oblige.

 

[JLGWhiz]

Thanks for your prompt reply. Your code is pulling sheet data in different sheets (need to pull data from different sheets to a single sheet namely "Master"). Also it couldn't manage to pull last row data.

Please extend your support and oblige.

I want to copy A8 to D (last row of Column D) to a new sheet with sheet name.

Two things, your data range description in the OP does not match the data ranges of the sheets in the link provided. Secondly, The quote from the OP in red fornt above indicates the need for a new sheet for each copy action. What you need to do is state your objective clearly, using the range parameters of your actual files (starting row/column) and the number of workbooks involved (There is no "Master" sheet in the file in the link above) and if they are not in the same folder, provide the paths for the one that does not contain the "Master" sheet. Then I can get the code modified to do what you want.

 

[JLGWhiz]

This assumes that sheet 'Master' is in the same workbook as the 'Stock Code' sheets and that data begins on row 10 as in the sheets in the linked file.

Sub t()
Dim sh As Worksheet, rng1 As Range, rng2 As Range, cnt1 As Long, cnt2 As Long
    For Each sh In ThisWorkbook.Sheets
        If sh.Name Like "Stock Code*" Then
            Set rng1 = sh.Range("A10", sh.Cells(Rows.Count, 4).End(xlUp))
            Set rng2 = sh.Range("F10", sh.Cells(Rows.Count, "K").End(xlUp))
            cnt1 = rng1.Rows.Count
            cnt2 = rng2.Rows.Count
            rng1.Copy Sheets("Master").Range("B2")
            Sheets("Master").Range("A2").Resize(cnt1) = sh.Name
            rng2.Copy Sheets("Master").Cells(Rows.Count, 2).End(xlUp)(2)
            Sheets("Master").Cells(Rows.Count, 1).End(xlUp)(2).Resize(cnt2) = sh.Name
        End If
    Next
End Sub