Results 1 to 5 of 5

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

This is a discussion on VBA - copy rows of data from one sheet to another where one within the Excel Questions forums, part of the Question Forums category; Hi all, I have one sheet with a few 1000 lines in it and I want to split this data ...

  1. #1
    New Member
    Join Date
    Jul 2002
    Posts
    17

    Default

    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

  2. #2
    MrExcel MVP TommyGun's Avatar
    Join Date
    Dec 2002
    Location
    Clear Lake, TX
    Posts
    4,202

    Default

    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 ]

  3. #3
    New Member
    Join Date
    Aug 2009
    Posts
    1

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

    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

  4. #4
    New Member
    Join Date
    Oct 2009
    Location
    Wisconsin
    Posts
    1

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

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

    ActiveCell.Offset(1, 0).Activate



  5. #5
    New Member
    Join Date
    Mar 2009
    Posts
    2

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

    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



    Jan Feb
    Dallas labor 500 500
    Dallas vehicle 210 210
    Austin tax 100 100
    Austin benefits 20 20
    Austin labor 500 500

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com