Create reports from multiple sheets

KonVba

New Member
Joined
Sep 19, 2014
Messages
3
Hello I'm new in the forum.
I have a problem with a workbook how to create some reports.
I had post my question to another vba- forum but no answer to fit me!!!!
SO I please for your help.

I have a workbook with more than 12 sheets. Sheet names are month of a year. The months starts from September until next August. The sheets names are like 09_14, 10_14 .... 08_15
In every month sheet i have the same template. Col A is numbering from A3 to ....., col B is the names, col C is value 1, col D is value 2, col E is value 3, col f is value 4, col G is remarkA, col H is remark B.

What I want is 3 types of reports:
1. Search between months (start - end) for a specific name (col. B), and the result (col B until col H) should go to a sheet named report.
2. In every sheet some names have a specific remark col G-H. The remarks are "AA" or "BB". Search for all AA or BB between months (start - end) and the result (col B until col H) should go to a sheet named report.
3. I have a sheet Year. That sheets add every month some sums (col C-F) of every month sheet. I want to Search between months (start - end) and the result (month sum Col C-F) should go to a sheet named report.

I appreciate for every help.
Maybe i can provide an attach file??
Thanks a lot for your time
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
I think you need to put ALL your data into one spreadsheet and then analyse it on your monthly sheets - is this possible?
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005

ADVERTISEMENT

see if you can amalgamate all data on a new sheet prior to analysis
 

Steph77

Board Regular
Joined
Sep 18, 2014
Messages
119
I haven't got the experience to write the code without spending ages debugging it (all my VBA coding involves writing something then adjusting it repeatedly until it works and takes ages - I'm a newbie myself), but I know the following is possible:

For (1)
Turn autofilter on and filter each sheet in turn for the specified name. Copy the results to another sheet. Report made.

For (2)
Same as for (1)

For (3)
I'm not sure I understand. if you just want selected months totals in a report instead of all months totals you could just hardcode to copy/paste only the columns/rows for the bits of data you want based on a user input for the range?
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
7these three tables are on sheets2, 3 and 4
the row counters above them automatically count the number of rowsthe macro finds the data and copies it to this sheet
578
namevalue1value2value3namevalue1value2value3namevalue1value2value3
N1456N6489N11141511
N2567N751112N12171812
N3678N8121415N13202113
N4789N971718N14232414
N58910N1082021N15262715
N1792324N19293016
N18102627N20323317
N21353618
macro code
Dim b(4)
rownum = 19
namevalue1value2value3 Sheets("Sheet2").Select
N1456 n = Cells(1, 1)
N2567 Sheets("sheet1").Select
N3678 For j = 3 To n + 2
N4789 rownum = rownum + 1
N58910 For k = 1 To 4
N6489 Sheets("Sheet2").Select
N751112 b(k) = Cells(j, k)
N8121415 Sheets("sheet1").Select
N971718 Cells(rownum, k) = b(k)
N1082021 Next k
N1792324 Next j
N18102627 Sheets("Sheet3").Select
N11141511 n = Cells(1, 1)
N12171812 Sheets("sheet1").Select
N13202113 For j = 3 To n + 2
N14232414 rownum = rownum + 1
N15262715 For k = 1 To 4
N19293016 Sheets("Sheet3").Select
N20323317 b(k) = Cells(j, k)
N21353618 Sheets("sheet1").Select
Cells(rownum, k) = b(k)
Next k
Next j
Sheets("Sheet4").Select
n = Cells(1, 1)
Sheets("sheet1").Select
For j = 3 To n + 2
rownum = rownum + 1
For k = 1 To 4
Sheets("Sheet4").Select
b(k) = Cells(j, k)
Sheets("sheet1").Select
Cells(rownum, k) = b(k)
Next k
Next j
End Sub

<colgroup><col span="16"></colgroup><tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,123,104
Messages
5,599,750
Members
414,333
Latest member
willfrederick

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