Results 1 to 11 of 11

Thread: Create report

  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Create report

    I need to create a report that extracts the data from cells A1 and C24 in a 50 sheet workbook
    cheers

    Phil Carter

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Phil,

    I assume you mean cells A1 & C24 from each worksheet in the workbook?
    How do you want the report to look? What columns / format.
    A little guidance please.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Just 2 columns which will be "Name" and "Date" in a new worksheet
    cheers

    Phil Carter

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Assuming that worksheet 1 is the report sheet:
    Code:
    Public Sub GrabData()
    Row = 2
    For I = 2 To Worksheets.Count
        With Worksheets(I)
        Cells(Row, 1) = .[a1]
        Cells(Row, 2) = .[c24]
        Row = Row + 1
        End With
    Next I
    End Sub

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    bonriki (2013-12-04)

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Phil,

    Building on Maud's fine work you might want to add statements to skip the Report Sheet:
    Code:
    Public Sub GrabData()
    Row = 2
    For I = 2 To Worksheets.Count
        With Worksheets(I)
           If .Name <> "Your Report Tab name here" Then '<----
             Cells(Row, 1) = .[a1]
             Cells(Row, 2) = .[c24]
             Row = Row + 1
           End If                                                                           '<----
        End With
    Next I
    End Sub
    Note: changes are Air Code but should be correct when you add the Tab name where indicated.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Maud
    Thanks again
    Works a treat
    cheers

    Phil Carter

  8. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Oh Retired one!
    Thanks for that
    I inserted a sheet named "Driver Training" but the macro dumped the retrieved data into the sheet I started from
    cheers

    Phil Carter

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Phil,

    Code:
    Public Sub GrabData()
        Sheets("Driver Training").Select
        Row = 2
        For I = 2 To Worksheets.Count
        With Worksheets(I)
           If .Name <> "Driver Training" Then
             Cells(Row, 1) = .[a1]
             Cells(Row, 2) = .[c24]
             Row = Row + 1
           End If                                                                          
        End With
    Next I
    End Sub
    This should fix that. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. The Following User Says Thank You to RetiredGeek For This Useful Post:

    bonriki (2013-12-04)

  11. #9
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Yep
    That did it.
    Thanks again works great
    cheers

    Phil Carter

  12. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    RG,

    Clever tweak. Using your amended code, you might want to change the line to:

    For I = 1 To Worksheets.Count

    in case the "Driver Training" sheet is not the first sheet and you want the first sheet to be looked at.

    Maud
    Last edited by Maudibe; 2013-12-04 at 20:04. Reason: spelling

  13. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Maud,

    GOOD POINT!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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