Multiple lists feeding one named range

Jas · Sep 11th 2003

I have 7 employees who keep similar lists in Excel. Each list has the same four items in it (columns A through D). Column C is the date. There are anywhere from 0 to 100 line items entered into the list for each date.

I regularly open all seven worksheets, copy the NEW data, and paste it into another worksheet that has a combined list from all 7 people. I use the combined list to feed a pivot table.

I would like to automate this task... I'm open to suggestions on how to solve this...

A macro to open each file in turn, copy the new data, paste it at the bottom of the list is the only thing I've been able to think of. I'm not sure the best way to do it as I would want to specify for the macro to ONLY grab the data with a specific date in each of the 7 "child" worksheets to copy them into the "parent" worksheet.

If there is another way to accomplish this without using a macro, that's good too. Any and all suggestions are welcome.

Thanks in advance!

Barrie Davidson · Sep 11th 2003

Hi Jas, would this code work for you?

Code

Sub CombineData()
' Written by Barrie Davidson
' Sep 11, 2003
Dim FilesToOpen
Dim DateToExtract As Date
Dim LastRow As Long
Dim CopyDest As Range




FilesToOpen = Application.GetOpenFilename
If IsArray(FilesToOpen) = False Then Exit Sub
Application.ScreenUpdating = False
DateRoutine:
On Error GoTo DateRoutine
DateToExtract = CDate(InputBox("Enter date to extract"))
On Error GoTo 0
For i = 1 To UBound(FilesToOpen)
    CopyDest = Workbooks("Summary.xls").Sheets("Sheet1") _
        .Range("A65536").End(xlUp).Offset(1, 0)
    Workbooks.Open FilesToOpen(i)
    LastRow = ActiveSheet.Range("A65536").End(xlUp).Row
    ActiveSheet.Range("A1:D" & LastRow).AutoFilter _
        Field:=3, Criteria1:="=" & DateToExtract
    Range("A2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy _
        Destination:=CopyDest
    Application.DisplayAlerts = False
    ActiveWorkbook.Close False
    Application.DisplayAlerts = True
Next i
Application.ScreenUpdating = True


End Sub

Display More

This code assumes that your summary workbook is named "Summary.xls" and you are pasting the data in the worksheet named "Sheet1". Just make whatever changes you need.

Let me know if this meets your requirements.

Regards,

Jas · Sep 12th 2003

That looks pretty slick... My VBA isn't strong enough for me to understand everything that's going on, so I'll spend some time with my nose in a book to figure it out! :wink2:

I'm particularly confused as to how this macro would know what files to open.

I also don't know what this line does: On Error GoTo 0

Other than that, I have at least partial clues...

**royUK** · Sep 12th 2003

On error go to 0 is a to prevent error messages if there is no data.
Just a quick look at Barrie's code you will be offered the open workbook dialog to open the file that you wish to copy from. You will then have to indicate what you want to copy

Barrie Davidson · Sep 12th 2003

Quote

Originally posted by Jas
That looks pretty slick... My VBA isn't strong enough for me to understand everything that's going on, so I'll spend some time with my nose in a book to figure it out! :wink2:

I'm particularly confused as to how this macro would know what files to open.

I also don't know what this line does: On Error GoTo 0

Other than that, I have at least partial clues...

Display More

Feel free to post any other questions you have on the code. I (or anyone else on this board :biggrin: ) will be more than happy to help you out.

Sincerely,

Jas · Sep 12th 2003

:biggrin:

Thanks, don't worry, I WILL be back frequently! :wink2:

In the mean time, I've also ordered one of the books reccomended here, and can't wait for it to show up...

Multiple lists feeding one named range

Participate now!

Share