Compile Data From Multiple WorkSheets

[fasterthanyours]

I'm working in Excel 2010.

Ok I'm using a VBScript from another Spreadsheet and I'm just not getting it right.

I've got the similar Data in 10+ Spreadsheets and I want to compile it on one running a script. The current sheet names will stay to same, but I want to add more down the road.

Current Worksheet names are:
Columbia, SC
Durham, NC
Orlando, FL
Norcross, GA
Alabama
Kentucky
Knoxville
Nashville
West Virginia

The column range on each sheet is B to P.

The starting Row is 4

Like I said before I'm using a compile script from another project.

The data needs to be placed in Column A beginning at Row 3.

Here's the code I have so far:

Please Login or Register  to view this content.

[fasterthanyours]

I forgot to say that current it displays the data as the following:

Cooper Jr., Phillip elton Columbia-Field
Fisher, Timothy Adam Columbia-Field

uddy Flippin Durham-Field
ichard Gregory Durham-Field
Peter eynolds Durham-Field
oger (ryon) oorefield Durham-Field

The first group is correct, but the second group is missing letters.

[fasterthanyours]

Anyone have any idea on this?

[fasterthanyours]

Ok so I removed the following lines:

rChrs = Array("RH", "LH", "R", "L", "KC", "HH", "D", "M", "B")

and

If Not IsNumeric(s) Then
For j = 0 To UBound(rChrs)
s = Replace(s, rChrs(j), "")
Next
End If

After I did that my missing letters came back. I added in the rest of my worksheets and the first 2 columns appear from each sheet.

I've been playing with the code for about 30 minutes now and I can't figure out how to get more than 2 columns imported in to my worksheet.

[snb]

Please Login or Register  to view this content.

[fasterthanyours]

snb?

What am I supposed to do with that? Where to put it?

[snb]

Using it as a macro ?

[fasterthanyours]

Ok Thanks, but that's not what I need. It imported everything off every sheet.

I have other sheets in the Spreadsheet that contain different data sets that I don't need. Since it imported everything, it also go the Header information as well and I don't need that either.

[snb]

So you have to adapt my code a little.

[fasterthanyours]

Ok I'm getting no where with this.

Any thoughts on modifying my original code to include more than the first 2 columns?