Solved: Pulling data from seperate worksheets into cumulative one.

[Beatrix]

Dear All ,

I have a spreadsheet in Excel 2010. There are 16 tabs for data entry and all in same format. I need to create a dynamic reporting system based on cumulative data of this 16 tabs. I thought I can use a pivot table by using cumulative data source to produce the reports. However I don't know how to pull data from 16 tabs into cumulative tab by creating a dynamic structure. I could use excel vba but I'm still beginner level and not sure how to do it. Do you have any other suggestions on this? or could you help me about how to do it?

I attached sample spreadsheet for this case.

Regards,
Yeliz

[mancubus]

hi yeliz.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=151

procedure here combines all ws's into one ws named "Master".

if you like you can change all "master"s in the proc to "cumulative" or another name by "ctrl+h" easily...

[Beatrix]

Hiya ,

Thanks very much for your response. I copied the code and run the macro and it created a blank worksheet named Master, didn't copy content. I thought I should change a range or something in the code as it might not recognise the input in worksheets. Also this process shouldn't cover the tab called "field". How can I exclude that tab?

An other question, after I copy the code into VBE I save the file; however It pops a message up saying I can't save it as a macro free workbook so it forces me to save it as xlsm file rather then xls file. How can I avoid to save it as an xlsm? I noticed that sample file in the link you sent me is an xls file so there should be a way to do that but I don't know:(

sorry about the details.

Cheers
Yeliz

[mancubus]

working for an int'l school, eh?

because first data cell in your table is B9, and in procedure it's assumed that cell is A1.

i've adopted the code so far...
delete sheet named cumulative.
sheet 8 first cell is D9. delete first two columns in order to suit other sheets table structure.

i added one column for worksheet names in order to dşsplay the origin of data.


Sub CopyFromWorksheets()
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=151

Dim wrk As Workbook 'Workbook object - Always good to work with object variables
Dim sht As Worksheet 'Object for handling worksheets in loop
Dim trg As Worksheet 'Master Worksheet
Dim rng As Range 'Range object
Dim colCount As Integer 'Column count in tables in the worksheets

Set wrk = ActiveWorkbook 'Working in active workbook

For Each sht In wrk.Worksheets
If sht.Name = "Master" Then
MsgBox "There is a worksheet called as 'Master'." & vbCrLf & _
"Please remove or rename this worksheet since 'Master' would be" & _
"the name of the result worksheet of this process.", vbOKOnly + vbExclamation, "Error"
Exit Sub
End If
Next sht

'We don't want screen updating
Application.ScreenUpdating = False

'Add new worksheet as the last worksheet
Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count))
'Rename the new worksheet
trg.Name = "Master"
'Get column headers from the first worksheet
'Column count first
Set sht = wrk.Worksheets(1)
colCount = sht.Cells(9, Columns.Count).End(xlToLeft).Column
'Now retrieve headers, no copy&paste needed
trg.Cells(1, 1) = "Sheet Name"
trg.Cells(1, 1).Font.Bold = True
With trg.Cells(1, 1).Resize(1, colCount - 1)
.Value = sht.Cells(9, 2).Resize(1, colCount - 1).Value
'Set font as bold
.Font.Bold = True
End With

'We can start loop
For Each sht In wrk.Worksheets
'If worksheet in loop is the last one, stop execution (it is Master worksheet)
If sht.Index = wrk.Worksheets.Count Then
Exit For
End If
If sht.Name <> "fields" Then
'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets
Set rng = sht.Range(sht.Cells(10, 2), sht.Cells(Rows.Count, 2).End(xlUp).Resize(, colCount - 1))
'Put data into the Master worksheet
trg.Cells(Rows.Count, 2).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
'Worksheet names to column A
trg.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, 1).Value = sht.Name
End If
Next sht
'Fit the columns in Master worksheet
trg.Columns.AutoFit

'Screen updating should be activated
Application.ScreenUpdating = True

End Sub

[Beatrix]

Hi mancubus ,

It's brilliant! working perfectly now.. I must learn how to write VBA code as soon as possible. This makes life much easier.. I love this forum.

I have only one question; when I want to save it, it gives a message saying that " The following features cannot be saved in macro-free workbooks:
VB project
To save a file with these features, click No, and then choose a macro-enabled fie type in the File Type list.
To continue saving as a macro-free workbook, click Yes."

How can I save the file as an xlsx with this macro?

I really appriciate for all your help...

Cheers
Yeliz

file:///C:/Users/YTOZUN/AppData/Local/Temp/moz-screenshot-1.png

file:///C:/Users/YTOZUN/AppData/Local/Temp/moz-screenshot.png

[mancubus]

Hi Yeliz.

save the file as macro-enabled workbook.

press F12 function key (or save as from file for office 2010 or office button - save as for office 2007) to open save as dialog, choose Excel Macro-Enabled Workbook from "save as type".

pls mark the thread as solved from thread tools..

[Beatrix]

Hi ,

in xls files it doesn't require changing file type when we run a macro in excel 2003. The link you emailed me has a sample file and file type is xls. I thought saving xlsx as an xlsm would change the functionality of the workbook. I think it works this way only for 2007 and onwards..

Thanks..I'll mark the thread as solved..

Yeliz

[mancubus]

yep, you're rite.

office 2007 and 2010 have different file extensions that previous versions don't.