SplitToSheets()

[andy.bard]

Hello, I am trying to use the vba code below to copy and paste simmilar data from a very large excel sheet where column A has unique identifiers and this is how I would like to seprate my different excel sheets that will be created with the vba code. But i get stuck on "Set stName = Sheets(idName)" at this part of the code and get the error message run-time error '9': subscript out of range. Please help!


Sub SplitToSheets()
Dim stOrig As Worksheet
Dim stName As Worksheet
Dim idName As Variant
Dim lnCont As Long
Dim idCont As Long
Application.ScreenUpdating = False
lnCont = 0
idCont = 0
Set stOrig = Sheets("Sheet1") 'replace sheet1 with your sheet name
lnCont = Application.CountA(stOrig.Columns(1))
For i = 2 To lnCont
idName = stOrig.Cells(i, 1) 'replace 2 with the column that the ID is in
Set stName = Sheets(idName)
idCont = Application.CountA(stName.Columns(1))
stOrig.Rows(i).Copy Destination:=stName.Rows(idCont)

idName = ""
idCont = 2
Set stName = Nothing
Next i
Application.ScreenUpdating = True
Set stOrig = Nothing
End Sub

 

[Datsmart]

What do you have in Column A as your "unique identifiers"?
As your code is written, it is looking for a WorkSheet Index Number.
The index number is a sequential number assigned to a sheet, based on the position of its sheet tab (counting from the left) among sheets of the same type.

 

[andy.bard]

Thanks for your responce. Here is an example of my data. This excel sheet is over 60k records long and 15 total columns but as an example of what I am working with. I would like to create a new worksheet for each Area_ID and us the Area_ID as the unique identifier in the script. There could be 20 Area_IDs or 100 depending on the month.

<TABLE style="WIDTH: 297pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=394><COLGROUP><COL style="WIDTH: 86pt" width=114><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6070" width=166><COL style="WIDTH: 86pt" width=114><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #bfbfbf; WIDTH: 86pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=114>Area_ID</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #bfbfbf; WIDTH: 125pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=166>LETTER</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #bfbfbf; WIDTH: 86pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=114>P_ID</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>A051</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>CTS</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>0191</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>A051</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>CTS</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>0186</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>A038</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>CTD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>0004</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>A038</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>CTD</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>0006</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>A051</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>CTS</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>0034</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>A051</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>CTS</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>0001</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>A026</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>CTG</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>0005</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>A001</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>CTA</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>0002</TD></TR></TBODY></TABLE>

 

[hiker95]

andy.bard,

What is the worksheet name containing your raw data?

See:

Norie
DistributeRows
http://www.mrexcel.com/forum/showthread.php?t=315083

 

[andy.bard]

Thank you for the solutions.

This worked like a charm and ran in under a min. One last thing i wanted to have done. Can I put in the code to create a new excel workbook for each of the unique identifiers. As the code is written now it created new worksheets in the existing workbook.

 

[hiker95]

andy.bard,


Now that you have created all the worksheets in your workbook, see:


Sheets to WB(1)
https://sites.google.com/a/madrocke...celassistant/parse-functions/each-sheet-to-wb

Specifications:
1.One workbook with many sheets
2.Need a separate workbook for each sheet so they can be sent to various departments without sending whole wb.
3.Macro is run frequently, so each time it is run the workbooks created include the date in the name.
4.Original workbook has formulas, created workbooks should be formula-free, flat values only.

 

[andy.bard]

Thank you for all your help. The second code worked like a charm just a s the first one did. This has saved me many days of manually doing the sepration each month.

 

[hiker95]

andy.bard,

You are very welcome.

Glad I could help.

Thanks for the feedback.

Come back anytime.