none
EXTREMELY NEW to VBA, need rows containing specific data from two sheets into one new one. RRS feed

  • Question

  •    
    I need to pull from shared worksheets CALENDAR and NEW ORDERS the entire rows that contain "Sanderson" in Column B and move them to new sheet, SANDERSON.
    I know its possible, but for every one I try to write, I am fully wrong. If possible for anyone to help. Can you explain the steps as well? I have several more of these types to write and I would love to do them on my own, just need a starting point.

    CALENDAR and NEW ORDERS are separate files entirely on our computer, and there is no way to make them one workbook. I don't know if that is where I am running into a wall or not. also, worth mentioning, CALENDAR and NEW ORDERS are updated several times a day by several different people in the office as they are stored on a share drive. I would love to have them pull two to three times a day automatically (this may be asking too much), but this is not a necessity. someone please help me! It will save me much more hair pulling out of frustration
    Tuesday, November 12, 2013 3:07 PM

All replies

  • Copy the macro and place it into your personal.xls file.  With both workbooks open, select one of the cells in Calendar that contains "Sanderson" (or any value that you want) and run the macro. I have assumed that the workbooks have only one sheet, and both are xlsx files. If you are working in XL2003, change xlsx to xls

        

    Sub TestMacro()
        Dim rngC As Range        ' The cell found with what you want
        Dim rngD As Range        ' All the cells found with what you want
        Dim strFind As String
        Dim strFAdd As String
        Dim myB As Variant
        Dim wkbkN As Workbook

        strFind = ActiveCell.Value
        Set wkbkN = Workbooks.Add
        wkbkN.Worksheets(1).Name = strFind

        For Each myB In Array("Calendar.xlsx", "New Orders.xlsx")

            With Workbooks(myB).Sheets(1).Range("B:B")
                Set rngD = Nothing
                Set rngC = Nothing

                Set rngC = .Find(strFind, LookIn:=xlValues, lookAt:=xlWhole)

                If Not rngC Is Nothing Then
                    Set rngD = rngC
                    strFAdd = rngC.Address
                Else:
                    MsgBox strFind & " was not found in """ & myB & """"
                    GoTo doNext
                End If

                Set rngC = .FindNext(rngC)
                If Not rngC Is Nothing And rngC.Address <> strFAdd Then
                    Do
                        Set rngD = Union(rngD, rngC)
                        Set rngC = .FindNext(rngC)
                    Loop While Not rngC Is Nothing And rngC.Address <> strFAdd
                End If
            End With

            rngD.EntireRow.Copy wkbkN.Worksheets(1).Cells(Rows.Count, "B").End(xlUp)(2).EntireRow

    doNext:
        Next myB

        wkbkN.SaveAs strFind & ".xlsx"

    End Sub

    Tuesday, November 12, 2013 8:22 PM
  • Thanks, this only pulled one piece of a row, I was trying to find out how to get all of the rows in the worksheet where Column B contains SANDERSON copied. It is only pulling A B and C from the line where I start. Please advise.

    Thursday, November 14, 2013 1:27 PM
  • My code cannot copy part of a row.  Are both workbooks Calendar and New Orders open? Are they named Calendar.xlsx and New Orders.xlsx? Is SANDERSON always in column B? On the first sheet of each workbook?

    What do the messages give your?

    Sub TestMacro2()
        Dim rngC As Range        ' The cell found with what you want
        Dim rngD As Range        ' All the cells found with what you want
        Dim strFind As String
        Dim strFAdd As String
        Dim myB As Variant
        Dim wkbkN As Workbook

        strFind = ActiveCell.Value
        If MsgBox("Are you sure you want to look for """ & strFind & """", vbYesNo) = vbNo Then Exit Sub
        Set wkbkN = Workbooks.Add
        wkbkN.Worksheets(1).Name = strFind

        For Each myB In Array("Calendar.xls", "New Orders.xls")

            With Workbooks(myB).Sheets(1).Range("B:B")
                Set rngD = Nothing
                Set rngC = Nothing

                Set rngC = .Find(strFind, LookIn:=xlValues, lookAt:=xlWhole)

                If Not rngC Is Nothing Then
                    Set rngD = rngC
                    strFAdd = rngC.Address
                    MsgBox strFind & " was first found in cell " & rngC.Address(False, False, xlA1, True)
                Else:
                    MsgBox strFind & " was not found in """ & myB & """"
                    GoTo doNext
                End If

                Set rngC = .FindNext(rngC)
                If Not rngC Is Nothing And rngC.Address <> strFAdd Then
                    Do
                        Set rngD = Union(rngD, rngC)
                        MsgBox strFind & " was also found in cell " & rngC.Address(False, False, xlA1, True)
                        Set rngC = .FindNext(rngC)
                    Loop While Not rngC Is Nothing And rngC.Address <> strFAdd
                End If
            End With

            MsgBox "I am now copying rows " & rngD.EntireRow.Address(False, False, xlA1, True)
            rngD.EntireRow.Copy wkbkN.Worksheets(1).Cells(Rows.Count, "B").End(xlUp)(2).EntireRow

    doNext:
        Next myB

        wkbkN.SaveAs strFind & ".xlsx"

    End Sub

    Thursday, November 14, 2013 1:50 PM
  • Yes, I have them both open.

    Yes those are their names.

    Always in Column B

    Only the one sheet is active for each these two workbooks.

    Friday, November 15, 2013 7:12 PM
  • And what do the message boxes show when you run the macro from the last message?
    Friday, November 15, 2013 7:34 PM
  • "Run-time error '9':

    Subscript out of range

    I clicked on the 'Debug' option and it highlights the following line

             With Workbooks(myB).Sheets(1).Range("B:B")

    Friday, November 15, 2013 7:47 PM
  • and I was wrong the folder names are listed as CALENDAR, and NEW ORDERS. I changed them in the code when I ran it, they are actually named 'CAL 2013' and the other is person specific, the one I will be working with though is 'ELLEN 11 13' and the first sheet is named 'NewOrders'

    Friday, November 15, 2013 7:49 PM
  • For that error, the value of the variable myB does not match an actual workbook name - and I made a mistake - I was testing in 2003 and left an x off the workbook name.

    For Each myB In Array("Calendar.xls", "New Orders.xls")

    should be (try, at least)

    For Each myB In Array("Calendar.xlsx", "ELLEN 11 13.xlsx")

    Friday, November 15, 2013 8:24 PM