Results 1 to 10 of 10

How to copy last row of one sheet to another sheet auto....

This is a discussion on How to copy last row of one sheet to another sheet auto.... within the Excel Questions forums, part of the Question Forums category; we use a product at work that has about 25 different sheets on them. i want to make a sheet ...

  1. #1
    New Member
    Join Date
    Apr 2004
    Location
    japan
    Posts
    17

    Default How to copy last row of one sheet to another sheet auto....

    we use a product at work that has about 25 different sheets on them. i want to make a sheet that uses the last row of data on each sheet and puts all that information on 1 sheet (everytime the last row of information changes i need that other sheet to show the new information). i have no idea how to use that vba stuff. if anyone is willing to play with my product and see what they can do it would help me out alot thanks Jamie

  2. #2
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    Hi - welcome to the board

    maybe something like this. Lets say you want to put everything onto the "Summary Sheet" then

    Code:
    Sub Test()
    
        For x = 1 To Sheets.Count
            If Sheets(x).Name <> "Summary" Then
                y = Sheets(x).Range("A65536").End(xlUp).Row
                Sheets(x).Range("A" & y & ":IV" & y).Copy Destination:=Sheets("Summary").Range("A" & x)
                Else
            End If
        Next x
        
    
    End Sub

  3. #3
    New Member
    Join Date
    Apr 2004
    Location
    japan
    Posts
    17

    Default Re: How to copy last row of one sheet to another sheet auto.

    i honestly have no idea what you are saying. i have no idea how to do that code stuff. can you walk me through it?

  4. #4
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default Re: How to copy last row of one sheet to another sheet auto.

    Hi, Jacob has given you code that will copy the last rows and place them in a sheet called Summary. You need to place the code in a module within the Visual Basic Editor.

    Select Tools|Macro|Visual Basic Editor (or ALT+F11) then select Insert|Module from the menu. Copy Jacobs code (from Sub Test() to End Sub) and paste this in the right hand window. Select File|Save & Return to Excel to get back to your Excel sheet.

    To run the macro select Tools|Macro|Macros and click on the macro called Test and click the Run button.

    This code will only run when you tell it to as above so if you amend the last row or change the data in the last row nothing will happen. What is in the last row? A set of formulas in a summary sheet may be preferable.

  5. #5
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    If you want a formula you can use somethine like

    =INDEX(Sheet1!A:A,MATCH(9.99999999999999E+307,Sheet1!A:A,TRUE))

    To get the last value in column A on Sheet1

  6. #6
    New Member
    Join Date
    Apr 2004
    Location
    japan
    Posts
    17

    Default Re: How to copy last row of one sheet to another sheet auto.

    thanks i will try it out and post if it works for me

  7. #7
    New Member
    Join Date
    Apr 2004
    Location
    japan
    Posts
    17

    Default Re: How to copy last row of one sheet to another sheet auto.

    Sub Test()

    For x = 1 To Sheets.Count
    If Sheets(x).Name <> "Summary" Then
    y = Sheets(x).Range("A65536").End(xlUp).Row
    Sheets(x).Range("A" & y & ":IV" & y).Copy Destination:=Sheets("Summary").Range("A" & x)
    Else
    End If
    Next x


    End Sub

    gives me this error when i run it. does the names of each sheet neet to go somewhere? also i am getting words/numbers fromw my sheets. i can send an example file if it would help

  8. #8
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    What is the error?

    The only sheet that is named is Summary and must be spelled exactly as the summary sheet for you with the same case.

    If you cant get it working you can send me the wb.

    If it is a really large file please zip it first.

  9. #9
    New Member
    Join Date
    Apr 2004
    Location
    japan
    Posts
    17

    Default Re: How to copy last row of one sheet to another sheet auto.

    cool that part works now. but there are 3 pages that have no data on them. also i want colum "a" on the summary page to have information that does not change as well as row 10 with information that does not change how can i get it to paste in a specific location?

    i am at work now i can mail the file when i get home

  10. #10
    New Member
    Join Date
    Apr 2004
    Location
    japan
    Posts
    17

    Default Re: How to copy last row of one sheet to another sheet auto.

    ok..i got the macro working. but it is posting the summary page last line as well on the summary page. i have 20 sheets. how do i make it only look at those 20 sheets? thanks for all the help

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
  •  


DMCA.com