VBA - copy rows of data from one sheet to another where one

[bentleyr]

Hi all,

I have one sheet with a few 1000 lines in it and I want to split this data up onto roughly 40 sheets depending on the value in column B (a RepID). I am happy with coding to create the new sheet, copy data etc, but I would like to know the best way to code to get the correct selection for each sheet. The data is sorted and has 6 columns that I want to copy. Each ID is also the name of the new sheet which I know before I start so I can have the codes for the sheets saved elsewhere.

Any ideas would be appreciated.

Cheers Bentley

 

[TommyGun]

Well, you could try something like this:

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,2) '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

_________________
I'm not a programmer yet, but I'm working on it!
This message was edited by TommyGun on 2002-12-11 19:56

 

[kw274]

I know this is a bit of an old post but it does exactly what I'm looking for. The only problem is that when it pastes the row into the new sheet it always does it at row 1 ontop of what it last posted. I've been trying to mod the code so that it pastes on the next available row with no luck. Can anyone help please?

Thanks

 

[h.shelbrack]

Have you tried to add in the below code after the insert code?

ActiveCell.Offset(1, 0).Activate


<!--width:540px-->

 

[millert]

Very Newbie... I tried code above and am still having bug issues. Any help would greatly be appreciated. I have a set of data that includes location, a GL line then 12 months of data. Need a script to copy, create a new tab, and paste the data by location. It would be great if it could name the tab with location name. Not sure how to add a file, so...small example. Thank again. Thomas

<table style="border-collapse: collapse; width: 192pt;" border="0" cellpadding="0" cellspacing="0" width="256"><col style="width: 48pt;" span="4" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" height="17" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">Jan</td> <td style="width: 48pt;" width="64">Feb</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Dallas</td> <td>labor</td> <td align="right">500</td> <td align="right">500</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Dallas</td> <td>vehicle</td> <td align="right">210</td> <td align="right">210</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Austin</td> <td>tax</td> <td align="right">100</td> <td align="right">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Austin</td> <td>benefits</td> <td align="right">20</td> <td align="right">20</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Austin</td> <td>labor</td> <td align="right">500</td> <td align="right">500</td> </tr> </tbody></table>