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

gostlrs

New Member
Joined
Apr 13, 2004
Messages
17
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top