Code for large loop

[billybeane]

I have a difficult problem. I have a dynamic set of data that I run a program with, I end up with a sheet that looks like Sheet1.Sheet1.jpg

My goal is to take copy the information from each location to a new spreadsheet: for example I want all of the information from the Dallas store to copy to a folder on my desktop called Dallas and save in a spreadsheet called Dallas_Sales.xls. I have a second sheet which is just a listing of all my stores. Such as: Sheet2.jpg

I would love to have a loop that runs and automatically takes the data from each store and saves it in its own folder and sheet just like the Dallas example. I have some code that does this but it is very lengthy and difficult to maintain. An employee of mine with no excel knowledge runs this process for me now, and we are adding new stores all the time, so my goal is that they would only have to insert the name of that new store into Sheet2.

Any help would be greatly appreciated, I have been stuck on this forever!!!!



I have a some code that can move data such as:
ChDir _
"C:\Documents and Settings\Desktop\Dallas"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Desktop\Dallas\Dallas_Sales.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

I also have a copy if statement that will select only certain data such as:
Sub Copy2()
Dim i As Long
Dim lngLastRow As Long, lngPasteRow As Long

'Find the last row to search through
lngLastRow = Sheets("Sheet1").Range("A65535").End(xlUp).Row

'Initialize the Paste Row
lngPasteRow = 2

For i = 2 To lngLastRow
If Sheets("Sheet1").Range("A" & i).Value = "2" Then
Sheets("Sheet1").Select
Range("A" & i & ":IV" & i).Copy
Sheets("1%").Select
Range("A" & lngPasteRow & ":IV" & lngPasteRow).Select
ActiveSheet.Paste
lngPasteRow = lngPasteRow + 1
End If
Next i

End Sub

[TMS]

I can't help thinking you are just making life difficult for yourself. Excel works best with data in tables. I would recommend that you simply record all your raw data in one sheet and use filters and pivot tables for lists and analyses ... or just a summary sheet with some COUNTIF and SUMIF formulae.

Regards, TMS

[TMS]

Oh, and please read the forum rules and add code tags and, possibly, a more descriptive title.

Regards, TMS

[Sa DQ]

Please Login or Register  to view this content.

[billybeane]

Thanks everyone for the help so far, I think I have done a bad job of explaining.

My goal is for my loop to search through the values on Sheet 2, "Dallas" "Plano" "Frisco". For each one of those find the group of rows on sheet one that relate to that store, then copy into a new spreadsheet called Dallas Sales.xls in a folder on my desktop called Dallas.