I had a macro that would prompt the user to choose a file/workbook (x) to open up, and then the macro would take some data from workbook x and insert and paste the lines into workbook y. Instead of opening up workbook x, I decided to just put the needed tab from workbook x and manually add it as a tab in workbook y. I'm trying to modify my code so that it will still take the data need from the newly added tab in workbook y, and insert and paste those rows into another tab in workbook y. However, I keep getting an error saying "Object Doesn't Support this Property or Method." Any ideas on how to fix this would be greatly appreciated!
Above is the line is where the error appears when I step through the code.
Below is my whole code:
Option Explicit Sub VendorQuote() Dim wb1 As Workbook Dim rCount As Long Dim startRow As Long, rngFound As Range Dim wsABC As Worksheet Dim wsCD As Worksheet Dim wsVENDOR As Worksheet Dim arrColMap, x As Long, y As Long Dim rngData As Range Set wb1 = ThisWorkbook With wb1 Set wsABC = .Worksheets("Edge INTERNAL Quote") Set wsCD = .Worksheets("Contact Data") Set wsVENDOR = .Worksheets("VENDOR Quote") End With With wsCD.Columns("L") Set rngFound = .Find(What:=wsVENDOR, LookAt:=xlWhole, LookIn:=xlValues, MatchCase:=False) If Not rngFound Is Nothing Then startRow = rngFound.Offset(, 1).Value arrColMap = Split(rngFound.Offset(, 2).Value, ",") Else MsgBox "There was a problem, no match could be found for " & wsABC.Range("K5").Value Exit Sub End If End With With wsVENDOR 'Change the name to match your worksheet. Set rngData = .Range(.Cells(startRow, 1), .Cells(startRow - 1, 1).End(xlDown)) Set rngData = rngData.Resize(rngData.Rows.Count, UBound(arrColMap) + 1) rCount = rngData.Rows.Count wsABC.Rows("25:" & CStr(25 + rCount - 1)).EntireRow.Insert End With With rngData For x = LBound(arrColMap) To UBound(arrColMap) .Columns(x + 1).Copy Destination:=wsABC.Range(arrColMap(x) & 24) Next x End With MsgBox " quote conversion is complete." End Sub