pick up the last entry in all the sheets.

shyam

Active Member
Joined
Jul 18, 2005
Messages
302
I have 4-5 sheets wherein the datas are not uniform, i.e. sheet1 is of 20 lines, sheet2 is of 50 lines, sheet3 is of 75 lines etc.

The entries are made every day and gets added from time to time. I want to pick up the last entries in all the sheets and link them to a summary sheet separately.

Any possibility?

Regards

Shyam
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi shyam
insert a worksheet and rename it as summary
press Alt F8 and
paste the following code (macro)

Sub last()
For a = 1 To 3 (or as many sheets you have)
c = 1
Sheets(a).Select
For b = 1 To 25 (or maximum possible rows)
If Cells(b, 1) <> "" Then
c = c + 1
End If
Worksheets("summary").Cells(a, 1) = cells(c - 1,1)
Next b
Next a
End Sub

run this macro

You should get what you want

ravi
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

what would be the criterion for "last row" ?
do you want to copy the entire rows ?

this code assumes
the last entry can be found in column A
you want to copy the entire rows
Code:
Option Explicit

Sub add_to_summary()
Dim SumSH As Worksheet
Dim SH As Worksheet
Dim LR_SumSH As Long
Dim LR As Long

Set SumSH = Sheets("summary")

    For Each SH In Sheets
        If SH.Name <> SumSH.Name Then
        LR = SH.Cells(Rows.Count, 1).End(xlUp).Row
        LR_SumSH = SumSH.Cells(Rows.Count, 1).End(xlUp).Row
        SH.Rows(LR).Copy SumSH.Rows(LR_SumSH + 1)
        End If
    Next SH

End Sub
kind regards,
Erik
 

shyam

Active Member
Joined
Jul 18, 2005
Messages
302
Hi Erik.

I need to pick up the last entry from one particular column and not the entire rows. Similarly for other sheets also. Either it should be linked to summary or picked up and the figure should get reflected in the summary sheet as and when the data's are added/updated in other sheets.

Tks n rgds
Shyam
 

shyam

Active Member
Joined
Jul 18, 2005
Messages
302

ADVERTISEMENT

Mr. Ravishankar

Your code gives a compilation error on the following line:
Worksheets("summary").Cells(a, 1) = Cells(c - 1, 1)

Kindly correct me if I am wrong... I have sheets as follows:

First sheet is Summary
next sheet as Sheet1 - i have data from column A1 to A6
next sheet as Sheet2 - i have data from column A1 to A13
next sheet as Sheet3 - i have data from column A1 to A17
next sheet as Sheet4 - i have data from column A1 to A4
next sheet as Sheet5 - i have data from column A1 to A7

Would request you to edit/modify your macro and revert back ...

Tks n rgds
Shyam
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
His shyam
The error occured since c = 1 and c-1 was 0
I have modified the codes and it has worked for me.

Sub shyam()
For a = 1 To 5
Worksheets(a).Select
ActiveSheet.Cells.SpecialCells(xlLastCell).Select
ir = ActiveCell.Row
Worksheets("summary").Cells(3, a) = Cells(ir, 1)
Next a
End Sub

try your hands on it

ravi
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi Erik.

I need to pick up the last entry from one particular column and not the entire rows. Similarly for other sheets also. Either it should be linked to summary or picked up and the figure should get reflected in the summary sheet as and when the data's are added/updated in other sheets.

Tks n rgds
Shyam

if you want to pick the last entry in column A and put it at the bottom of column A in the summary sheet, then you only need to edit one line of my code
you can easily see where the copy is made (you don't need to be an expert to read "copy" !)
Code:
        SH.Rows(LR).Copy SumSH.Rows(LR_SumSH + 1)
replace it by
Code:
        SH.Cells(LR, 1).Copy SumSH.Cells(LR_SumSH + 1, 1)
probably you want only the value to be copied instead of formats ...
so better is
Code:
        SumSH.Cells(LR_SumSH + 1, 1) = SH.Cells(LR, 1)
final result
Code:
Option Explicit

Sub add_to_summary()
'Erik Van Geit
'061017
Dim SumSH As Worksheet
Dim SH As Worksheet
Dim LR_SumSH As Long
Dim LR As Long

Set SumSH = Sheets("summary")

    For Each SH In Sheets
        If SH.Name <> SumSH.Name Then
        LR = SH.Cells(Rows.Count, 1).End(xlUp).Row
        LR_SumSH = SumSH.Cells(Rows.Count, 1).End(xlUp).Row
        SumSH.Cells(LR_SumSH + 1, 1) = SH.Cells(LR, 1)
        End If
    Next SH

End Sub
greetings from Belgium,
Erik

EDIT: no "selections" are made: no "screen-flickering" and fast execution
 

shyam

Active Member
Joined
Jul 18, 2005
Messages
302
Mr. Erik.

Thank you very much.. it is working fine.


Mr. Ravi shankar
Your code is also working fine. but the results are placed horizontally, whereas i need it vertically, one below the other....If you can modify it for me would appreciate if very much.

tks n rgds
shyam
 

Forum statistics

Threads
1,136,507
Messages
5,676,263
Members
419,616
Latest member
quickflip

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
Top