Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Sheets Data to be Displayed in a Worksheet

  1. #1
    Board Regular
    Join Date
    Aug 2016
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sheets Data to be Displayed in a Worksheet


    Sheets Data to be Displayed in a Worksheet
    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

    here is the link of my workbook

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

    Thanks in advance..

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,840
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sheets Data to be Displayed in a Worksheet

    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
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    Board Regular
    Join Date
    Aug 2016
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sheets Data to be Displayed in a Worksheet

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,840
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sheets Data to be Displayed in a Worksheet

    Quote Originally Posted by arijitirf View Post
    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 by JLGWhiz; Apr 12th, 2019 at 04:11 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  5. #5
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,840
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Sheets Data to be Displayed in a Worksheet

    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
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •