Grabbing data from one workbook and pulling into another

[guamlenahans]

Hello,

I recieve a monthly spreadsheet of roughly 25000 names. Each name is listed by one of 400 codes and there are 20 columns of information tied to each name. The names I'm interested in will come from one of 5 codes.

On another sheet, I have 20 tabs, one for each regional location and the people located there.

I'm trying to write some sort of code/macro/vba that will read the monthly sheet, filter to a specific code (anyone of the 5), then filter to the regional location and update the sheet that is broken down by location.

I'm looking for a starting off point or template, not really sure where to begin.

Thanks in advance

rob

 

[bertie]

With the information given I can only give you some advice to get you started. Here is how I would approach your problem.

In you master workbook create a new worksheet.
Open the source workbook, loop through the data and test which meet the code criteria; which I have assumed column "A".
Copy the entire row into the new spreadsheet.
Close the source workbook.
Sort the new spradsheet, in the sample code below I have sorted on column "A", the code.

I have place constant variable at the top of the code for ease of editing:

[COLOR=green]'============================================[/COLOR]
[COLOR=green]'EDIT HERE[/COLOR]
[COLOR=darkblue]Const[/COLOR] sFilename = "C:\temp\25000 names.xls"
[COLOR=darkblue]Const[/COLOR] sDataSheetName = "Sheet1"     [COLOR=green]'data source sheet name[/COLOR]
[COLOR=darkblue]Const[/COLOR] sCode01 = "code1"
[COLOR=darkblue]Const[/COLOR] sCode02 = "code2"
[COLOR=darkblue]Const[/COLOR] sCode03 = "code3"
[COLOR=darkblue]Const[/COLOR] sCode04 = "code4"
[COLOR=darkblue]Const[/COLOR] sCode05 = "code5"
[COLOR=green]'============================================[/COLOR]

There is a MAIN procedure which controls the flow of the code:

[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] Main()
  ImportCodes
  SortData
  [COLOR=green]'UpdateSpreadsheets[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

The ImportCode procedure performs the actions outlined above:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] ImportCodes()
  [COLOR=darkblue]Dim[/COLOR] wb [COLOR=darkblue]As[/COLOR] Workbook
  [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
  [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
  [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]        [COLOR=green]'row counter[/COLOR]
  
  Worksheets.Add(After:=Worksheets(1)).Name = "ImportData"
  [COLOR=darkblue]Set[/COLOR] ws = Sheets("ImportData")
  
  [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open(sFilename)
  [COLOR=darkblue]Set[/COLOR] rng = wb.Sheets(sDataSheetName).Range("A1")
  
  [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rng = ""
    [COLOR=darkblue]If[/COLOR] rng.Value = sCode01 Or _
       rng.Value = sCode02 Or _
       rng.Value = sCode03 Or _
       rng.Value = sCode04 Or _
       rng.Value = sCode05 [COLOR=darkblue]Then[/COLOR]
       
       rw = rw + 1
       wb.Sheets(sDataSheetName).Range("A" & rng.Row & ":S" & rng.Row).Copy _
        Destination:=ws.Range("A" & rw)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
  
    [COLOR=darkblue]Set[/COLOR] rng = rng.Offset(1, 0)
  [COLOR=darkblue]Loop[/COLOR]
  
  wb.Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
  [COLOR=darkblue]Set[/COLOR] wb = [COLOR=darkblue]Nothing[/COLOR]
  [COLOR=darkblue]Set[/COLOR] rng = [COLOR=darkblue]Nothing[/COLOR]
  [COLOR=darkblue]Set[/COLOR] ws = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

The SortData procedure was created using the macro recorder.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] SortData()
    Sheets("ImportData").UsedRange.Sort _
      Key1:=Range("A1"), _
      Order1:=xlAscending, _
      Header:=xlNo, _
      OrderCustom:=1, _
      MatchCase:=False, _
      Orientation:=xlTopToBottom, _
      DataOption1:=xlSortNormal
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

As I said earlier I have sorted by code.
You would need to sort it by region.

The UpdateSpreadsheet procedure is the part I can't help you with as there is insufficient information to go on, i.e., regions, sheet names, etc.

From here it is just a matter of looping through the new sheet, testing the range each region spans and coping to the appropriate worksheet.

All of the code provided goes into the ThisWorkbook module.

Hope this helps,
Bertie