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

[gostlrs]

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

[DRJ]

Hi - welcome to the board

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

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

[gostlrs]

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?

[parry]

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.

[DRJ]

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

[gostlrs]

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

[gostlrs]

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

[DRJ]

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.

[gostlrs]

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

[gostlrs]

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