Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Moving specific data from one worksheet to another (Excel 97 - VBA)

    Hi Everyone!

    I have used VBA to ease up my reporting. I download data from our central database and then create worksheets for the end-users (The reports from the central database do not give the field users the info they need)

    So to my question........

    Each of my records has 1 of 7 names associated with it. All of the data needs to copied to it's own worksheet, so that each of the names has a detail sheet.
    Currently my code auto-filters the data on the main worksheet and copies the filtered data to the right worksheet. (All of John Doe's records go onto the John Doe sheet)

    This is really slow. It takes along time for my code to run.

    I am sure this isn't the most efficient way to do this. All I need is to copy the rows of data that have John Doe in column A to the John Doe sheet.

    Can someone point me in the right direction?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving specific data from one worksheet to another (Excel 97 - VBA)

    Would it be OK if the code sorts the worksheet before it does the copies? That will probably be the fastest. Also, is there a header row or rows at the top of the sheet that needs to be copied to each of the other sheets? If so, what row or rows?
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Moving specific data from one worksheet to another (Excel 97 - VBA)

    Hi Legare,

    Yes the code can sort the worksheet before the copy. There is a header row at the top of the sheet (row 1).

    Thanks!

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,944
    Thanks
    0
    Thanked 203 Times in 184 Posts

    Re: Moving specific data from one worksheet to another (Excel 97 - VBA)

    Hi awckie,

    You might be able to achieve what you want with filtering or formulae.

    On the attached workbook from my reply to one of your earlier posts, Sheet1 has filtering enabled. Choose the criterion on row1 for the name column, and only the data matching that criterion will be displayed.

    On sheet3 of the same workbook, changing the value in B1 changes the reported data, in effect doing much the same as filtering on Sheet1 (note that column C is hidden).

    Cheers
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Moving specific data from one worksheet to another (Excel 97 - VBA)

    Thanks for the reply, but I have to do this in VBA.

    This portion of my code is a small portion of a larger piece. It works right now, but it's sloooow. I just figured I was doing it the wrong way, 'cause I'm new at this.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Moving specific data from one worksheet to another (Excel 97 - VBA)

    You should be able to this with relatively few lines of VB code using the Advanced Filter method. I have attached a workbook (actually I took the liberty of using macropod's workbook - hope he does not mind) to demonstrate the idea.

    With your main data in a worksheet named Data,, the following code should filter a subset to the active sheet provided a criteria range and extract range are set up on the sheet.

    Sub DoFilter()
    Dim dataRange As Range
    Set dataRange = Sheets("Data").Range("A1").CurrentRegion
    dataRange.AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Range("A1:A2"), _
    CopyToRange:=Range("A4:F4"), _
    Unique:=False
    End Sub

    See attached wb,

    Andrew C
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Moving specific data from one worksheet to another (Excel 97 - VBA)

    I think that may just work.. I'll look into it and let you all know If I succed!

  8. #8
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Moving specific data from one worksheet to another (Excel 97 - VBA)

    Andrew! I could kiss you <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15>

    I put the Criteria in the proper worksheets during my first part of my code (Run via a Menu Command - I run one part, check for data accuracy and then run the rest)

    In the second portion I put in this sub

    Sub FiltertoSheets()

    Sheets("Adams Pipe").Select
    Dim ws As Worksheet
    For Each ws In Worksheets(Array("Adams Pipe", "Adams Fcst", "Jones Pipe", "Jones Fcst", "Doe Pipe", "Doe Fcst"))
    ws.Activate
    With ActiveSheet

    Sheets("FDD Consolidator").Columns("A:AA").AdvancedFilter Action:= _
    xlFilterCopy, CriteriaRange:=Range("A1:B2"), CopyToRange:=Range("A4"), _
    Unique:=False

    End With
    Next ws
    End Sub

    Thank you! Thank you! Thank you! Thank you!

  9. #9
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Moving specific data from one worksheet to another (Excel 97 - VBA)

    I just had to follow up to say Wow! I just ran my full report, and it used to take 4 minutes to run.

    Now it only takes 15 seconds!

    You guys are the best! I wouldn't have been able to even start on this project without all the help you have given me here and in the Excel forum...

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving specific data from one worksheet to another (Excel 97 - VBA)

    Try something like this:

    <pre>Public Sub CopyData()
    Dim I As Long, lFirst As Long, lLastRow As Long
    Dim strName As String
    Dim oWS As Worksheet
    lLastRow = Worksheets("Sheet1").UsedRange.Rows.Count
    Worksheets("Sheet1").Range("A1:IV" & lLastRow).Sort Key1:=Worksheets("Sheet1").Columns("A"), _
    Order1:=xlAscending, Header:=xlYes
    lFirst = 1
    Do While Worksheets("sheet1").Range("A1").Offset(lFirst, 0).Value <> ""
    strName = Worksheets("Sheet1").Range("A1").Offset(lFirst, 0).Value
    For I = lFirst To lLastRow
    If strName <> Worksheets("Sheet1").Range("A1").Offset(I + 1, 0).Value Then Exit For
    Next I
    Set oWS = Nothing
    On Error Resume Next
    Set oWS = Worksheets(strName)
    On Error GoTo 0
    If oWS Is Nothing Then
    Set oWS = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oWS.Name = strName
    End If
    oWS.Cells.ClearContents
    Worksheets("Sheet1").Range("1:1").Copy Destination:=oWS.Range("A1")
    Worksheets("sheet1").Range("A" & lFirst + 1 & " :A" & I + 1).EntireRow.Copy Destination:=oWS.Range("A2")
    lFirst = I + 1
    Loop
    End Sub
    </pre>


    This code assumes that the data to be moved is on a sheet named "Sheet1".
    Legare Coleman

  11. #11
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Moving specific data from one worksheet to another (Excel 97 - VBA)

    Legare,

    That's really cool, I like how the sheets are created based upon the name in column A. I was able to find a solution using the Advanced Filter (Except for one of my Macros's where it is causing Excel to crash... Doesn't make sense to me. 2 of my module's work fine with the code, but my third module crashes with it)

    Thank you...

Posting Permissions

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