Sheets Data to be Displayed in a Worksheet

arijitirf

Board Regular
Joined
Aug 11, 2016
Messages
67
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 NameDateMIGOUnitQty. Sheet NameIssue DateReq. No.UnitQty.
Stock Code 124-11-20145001040239No.2 Stock Code 126-12-201596No.2
Stock Code 105-03-20165001543431No.1 Stock Code 226-12-201597No.6
Stock Code 226-11-20145001042671No.6 Stock Code 202-01-2019GEN-20No.3
Stock Code 219-04-20165001591782No.3 Stock Code 308-12-201579No.3
Stock Code 222-01-20195002736188No.1578 Stock Code 326-12-2018EL-109No.52
Stock Code 318-12-20145001067389No.3 Stock Code 318-01-2019EL-145No.10
Stock Code 312-03-20165001551257No.2 Stock Code 321-01-2019EL-146No.1
Stock Code 315-12-20185002688227No.418 Stock Code 304-02-2019EL-162No.124
Stock Code 315-12-20185002688227No.218 Stock Code 304-02-2019EL-163No.22
Stock Code 418-12-20145001067427No.2 Stock Code 320-02-2019EL-203No.18
Stock Code 412-03-20165001551258No.1 Stock Code 322-02-2019EL-206No.58
Stock Code 408-01-20195002720336No.459 Stock Code 426-12-201596No.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..
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,794
Office Version
2013
Platform
Windows
Code:
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

Board Regular
Joined
Aug 11, 2016
Messages
67
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,794
Office Version
2013
Platform
Windows
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.
 
Last edited:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,794
Office Version
2013
Platform
Windows
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,369
Messages
5,450,010
Members
405,582
Latest member
Vathana

This Week's Hot Topics

Top