Summary sheet of most recent entry from multiple sheets

rachA · Feb 22, 2012

Hello,

I'm brand new to this forum, and very stuck with excel, so any help wil be greatly appreciated.

I have a workbook containing 36 sheets with info on individual birds, each with the bird ID as sheet name, and all with same format. Columns from A to G go; Date, Transmitter, Easting, Northing, Weight, Bill, Comment.

I would like to create a summary sheet which pulls the most recent entry from each worksheet, and compiles them with sheet name (which is also the bird ID) in column A, and the most recent data from that sheet in the adjacent cells, so that the summary has one row of most recent data for each bird.

I also need the summary sheet to be able to automatically update and recognise the new entries as I put them in.

I was hoping this code from another forum would do the trick, as it sounds like the person was after exactly the same thing as me...

"Hi
Insert a sheet called Summary and paste the following codes in the macro window ( Alt F11).

Code:

Sub Harley()Dim a As Long, x As Long, y As LongDim b As String For a = 1 To Sheets.Count b = Worksheets(a).Name If b <> "Summary" Then Sheets("Summary").Cells(a + 1, 1) = b x = Worksheets(a).Range("A65536").End(xlUp).Row Sheets("Summary").Cells(1, 1) = "=MATCH(MAX(" & b & "!A1:A" & x & ")," & b & "!A1:A" & x & ", 0)" y = Sheets("Summary").Cells(1, 1) Worksheets(a).Range("A" & y & ":Z" & y).Copy Sheets("Summary").Cells(a + 1, 2).PasteSpecial End If Next aEnd Sub</PRE>
run the macro. it lists sheet names in col A and pastes the most recent data of that sheet from B to AA."

But when I run this in the macro window it comes up with a "run time error 1004"

I have no idea why this is happening, so any help with re-writing this code to fix it, or a brand new code would be fantastic.

Thanks,
Rachael.

hiker95 · Feb 22, 2012

rachA,

Welcome to the MrExcel forum.

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum:
sensitive data scrubbed/removed/changed
what you have and what you expect to achieve

To attach screenshots, see below in my Signature block:
Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block:
You can upload your workbook to Box Net
mark the workbook for sharing
and provide us with a link to your workbook.

rachA · Feb 23, 2012

Thanks for those handy hints!

I'm using Excel 2010. Here is a sample of my worksheets...

Excel Workbook

A

B

C

D

E

F

G

1

Date

TX

Easting

Northing

Weight

Bill

Comment

2

21/10/2010

RELEASE

3

20/12/2010

31

1.55

86.9

HC

4

28/04/2011

30

2.1

87.1

TX

5

2/09/2011

15B

1.9

87.4

TX

AQ0809#1

Excel 2010

And here is what I'd like the summary page to look like;

Excel Workbook

A

B

C

D

E

F

G

H

1

BirdID

Date

TX

Easting

Northing

Weight

Bill

Comment

2

AD0910#2

27/09/2011

57B

1.44

na

TX

3

AQ0809#1

summary

Excel 2010

Any help would be grand.

Thanks,
Rach.

hiker95 · Feb 23, 2012

rachA,

Can we have a screenshot of worksheet AD0910#2?

And, maybe one or two more raw data worksheets?

rachA · Feb 23, 2012

Hi Hiker95,

Yep sure...here are a few more

Excel Workbook

A

B

C

D

E

F

G

1

Date

TX

Easting

Northing

Weight

Bill

Comment

2

21/10/2010

65 Y

RELEASE

3

16/12/2010

01 Y

1.075

70.6

TX

4

27/09/2011

57B

1.44

na

TX

AD0910#2

Excel 2010

Excel Workbook

A

B

C

D

E

F

G

1

Date

Easting

Northing

Weight

Bill

Comment

2

21/10/2010

RELEASE

3

4/11/2010

4

25/11/2010

20

1.425

n.a

5

22/03/2011

25

2.165

92.4

TX

6

27/10/2011

25

2.175

105.1

AQ0910#1 Pete

Excel 2010

Excel Workbook

A

B

C

D

E

F

G

1

Date

TX

Easting

Northing

Weight

Bill

Comment

2

21/10/2010

RELEASE

3

25/10/2010

4

22/11/2010

62

1.39

78.5

HC

5

23/03/2011

62

1.4

81.2

6

31/08/2011

57

1.7

87.4

TX

7

26/10/2011

57

1.675

89.8

AX0910#1

Excel 2010

As you can see, each sheet has the same setup with column headers, but they have a variable number of data entries, which is why I need a code that will use the most recent date, or entry lowest down the list, to pick up the data for the summary sheet, and automatically update this in the summary sheet if new data is added into the data sheets.

Thanks again for you help with this.

Rach.

hiker95 · Feb 23, 2012

rachA,

Sample worksheets:

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

1

Date

TX

Easting

Northing

Weight

Bill

Comment

2

21/10/2010

65 Y

RELEASE

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

3

16/12/2010

01 Y

1.075

70.6

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

4

AD0910#2

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

1

Date

TX

Easting

Northing

Weight

Bill

Comment

2

21/10/2010

RELEASE

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

3

20/12/2010

31

1.55

86.9

HC

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

4

28/04/2011

30

2.1

87.1

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

5

AQ0809#1

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

1

Date

Easting

Northing

Weight

Bill

Comment

2

21/10/2010

RELEASE

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

3

4/11/2010

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

4

25/11/2010

20

1.425

n.a

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

5

22/03/2011

25

2.165

92.4

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

6

AQ0910#1 Pete

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

1

Date

TX

Easting

Northing

Weight

Bill

Comment

2

21/10/2010

RELEASE

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

3

25/10/2010

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

4

22/11/2010

62

1.39

78.5

HC

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

5

23/03/2011

62

1.4

81.2

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

6

31/08/2011

57

1.7

87.4

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

7

AX0910#1

After the macro:

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

1

BirdID

Date

TX

Easting

Northing

Weight

Bill

Comment

2

AD0910#2

16/12/2010

01 Y

1.075

70.6

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

3

AQ0809#1

28/04/2011

30

2.1

87.1

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

4

AQ0910#1 Pete

22/03/2011

25

2.165

92.4

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

5

AX0910#1

31/08/2011

57

1.7

87.4

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

6

Summary

hiker95 · Feb 23, 2012

rachA,

Then you add one row to each of the Sample worksheets:

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

1

Date

TX

Easting

Northing

Weight

Bill

Comment

2

21/10/2010

65 Y

RELEASE

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

3

16/12/2010

01 Y

1.075

70.6

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

4

27/09/2011

57B

1.44

na

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

5

AD0910#2

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

1

Date

TX

Easting

Northing

Weight

Bill

Comment

2

21/10/2010

RELEASE

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

3

20/12/2010

31

1.55

86.9

HC

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

4

28/04/2011

30

2.1

87.1

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

5

02/09/2011

15B

1.9

87.4

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

6

AQ0809#1

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

1

Date

Easting

Northing

Weight

Bill

Comment

2

21/10/2010

RELEASE

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

3

4/11/2010

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

4

25/11/2010

20

1.425

n.a

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

5

22/03/2011

25

2.165

92.4

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

6

27/10/2011

25

2.175

105.1

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

7

AQ0910#1 Pete

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

1

Date

TX

Easting

Northing

Weight

Bill

Comment

2

21/10/2010

RELEASE

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

3

25/10/2010

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

4

22/11/2010

62

1.39

78.5

HC

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

5

23/03/2011

62

1.4

81.2

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

6

31/08/2011

57

1.7

87.4

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

7

26/10/2011

57

1.675

89.8

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

8

AX0910#1

Before running the macro again, worksheet Summary goes from this:

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

1

BirdID

Date

TX

Easting

Northing

Weight

Bill

Comment

2

AD0910#2

16/12/2010

01 Y

1.075

70.6

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

3

AQ0809#1

28/04/2011

30

2.1

87.1

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

4

AQ0910#1 Pete

22/03/2011

25

2.165

92.4

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

5

AX0910#1

31/08/2011

57

1.7

87.4

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

6

Summary

To this:

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

1

BirdID

Date

TX

Easting

Northing

Weight

Bill

Comment

2

AD0910#2

27/09/2011

57B

1.44

na

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

3

AQ0809#1

02/09/2011

15B

1.9

87.4

TX

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

4

AQ0910#1 Pete

27/10/2011

25

2.175

105.1

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

5

AX0910#1

26/10/2011

57

1.675

89.8

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

6

Summary

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Option Explicit
Sub CreateSummary()
' hiker95, 02/23/2012
' http://www.mrexcel.com/forum/showthread.php?t=615834
Dim wsr As Worksheet, ws As Worksheet
Dim lr As Long, nr As Long
Application.ScreenUpdating = False
If Not Evaluate("ISREF(Summary!A1)") Then Worksheets.Add(Before:=Worksheets(1)).Name = "Summary"
Set wsr = Worksheets("Summary")
wsr.UsedRange.Clear
wsr.Range("A1:H1").Value = Array("BirdID", "Date", "TX", "Easting", "Northing", "Weight", "Bill", "Comment")
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Summary" Then
    lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
    If lr > 1 Then
      nr = wsr.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      wsr.Cells(nr, 1) = ws.Name
      ws.Cells(lr, 1).Resize(, 27).Copy wsr.Cells(nr, 2)
    End If
  End If
Next ws
wsr.UsedRange.Columns.AutoFit
wsr.Activate
Application.ScreenUpdating = True
End Sub

Then run the CreateSummary macro.

hiker95 · Feb 23, 2012

rachA,

I also need the summary sheet to be able to automatically update and recognise the new entries as I put them in.

This is going to be difficult, because, what cell change will trigger the macro for each worksheet?

You will have to run the macro manually each time you want to update the Summary worksheet.

rachA · Feb 23, 2012

YAY!!! It works perfectly

Thank you so much. It's no problem to run the macro each time I update the individual sheets.

Thanks again, I really appreciate your help with this.

hiker95 · Feb 23, 2012

racha,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.

Summary sheet of most recent entry from multiple sheets

rachA

New Member

Excel Facts

hiker95

Well-known Member

rachA

New Member

hiker95

Well-known Member

rachA

New Member

hiker95

Well-known Member

hiker95

Well-known Member

hiker95

Well-known Member

rachA

New Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

Summary sheet of most recent entry from multiple sheets

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock