I have a friend who has many Excel files, each containing data in four sheets. She wants to add the data in the sheets together, or even the data in different files, if there's a way for her to do that. We're using Office XP on a Windows 2000 computer, if that makes a difference in getting advice. Any way we can add these together?
Adding data in sheets
-
-
-
Hi This can be certainly done.. no probs.
I have worked with lot of these before. If you can give a few more pointers to the exact problems, it will be really great.
such as what is the structure of data, how many columns, how many sheets,
do you want to just dump the data one below the other or something else ... etch.See following code which have used to consolidate data on one sheet from different sheets in the same file.
Code
Display MoreSub consolidate() Sheets("Complete").Select ' Selects the sheet where consolidation is needed Range("Complete_Data_Clr").Clear ' clears existing data, you may skip this For Each Sheet In Sheets If Sheet.Name <> "Data Analysis" And Sheet.Name <> "Complete" And Sheet.Name <> "Entry Form" And Sheet.Name <> "Consolidate" Then ' Excluding some sheets which need not be consolidated nam = Sheet.Name data_nam = "=OFFSET(" & nam & "!R2C1,0,0,COUNTA(" & nam & "!R2C1:R4999C1),8)" ActiveWorkbook.Names.Add Name:=nam, RefersToR1C1:=data_nam ' creating name for data on that sheet. Works easy with other processing Sheets(nam).AutoFilterMode = False 'Remove filters if any existing Sheets(nam).Range(nam).Copy (Sheets("Complete").Range("A65536").End(xlUp).Offset(1, 0)) 'copy the named range below the last used cell End If Next End Sub
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!