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

    Home » Forums » AskWoody support » Productivity software by function » Visual Basic for Applications » Moving specific data from one worksheet to another (Excel 97 – VBA)

    Author
    Topic
    #383152

    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?

    Viewing 0 reply threads
    Author
    Replies
    • #652482

      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?

      • #652494

        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!

        • #652508

          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

          Cheers,
          Paul Edstein
          [Fmr MS MVP - Word]

          • #652612

            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.

            • #652650

              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

            • #652656

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

            • #652658

              Andrew! I could kiss you kiss

              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!

            • #652663

              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…

        • #652664

          Try something like this:

          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
          

          This code assumes that the data to be moved is on a sheet named “Sheet1”.

          • #652775

            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…

    Viewing 0 reply threads
    Reply To: Moving specific data from one worksheet to another (Excel 97 – VBA)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: