pick up the last entry in all the sheets.

[shyam]

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

 

[ravishankar]

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]

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

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]

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]

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]

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]

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" !)

        SH.Rows(LR).Copy SumSH.Rows(LR_SumSH + 1)

replace it by

        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

        SumSH.Cells(LR_SumSH + 1, 1) = SH.Cells(LR, 1)

final result

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]

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