I am struggling with this code, can seem to get it right. Can anyone help?
I have a spread sheet I use to cost track jobs by the PO #, Im trying to create a macro i can run at will to automate the data transfer.
Macro has to open a source workbook, search all of the worksheets for each occurrence of the PO# Range(A2) in the activesheet, each time it finds the PO copy the data from Col B, Col A, and Col K, paste that data back to the active sheet starting in the first available row of Col A
Code
Sub CopyData()
Dim ws As Worksheet, wb As Workbook
Dim searchValue As Variant
Dim rng As Range, cell As Range
Dim lastRow As Long
Set ws = ActiveSheet
searchValue = ws.Range("A2").Value
Set wb = Workbooks.Open("\\DATASERVER\data\DAILY SPREADSHEETS\Job Tickets\Job Ticket Log.xlsm")
Set rng = wb.Worksheets(3).Range("A:K").Find(searchValue, LookIn:=xlValues, LookAt:=xlWhole)
If Not rng Is Nothing Then
Do
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
rng.Offset(0, 11).Resize(1, 1).Copy
ws.Cells(lastRow, "A").PasteSpecial xlPasteValuesAndNumberFormats
rng.Offset(0, 0).Resize(1, 1).Copy
ws.Cells(lastRow, "B").PasteSpecial xlPasteValuesAndNumberFormats
rng.Offset(0, 2).Resize(1, 1).Copy
ws.Cells(lastRow, "C").PasteSpecial xlPasteValuesAndNumberFormats
Set rng = wb.Worksheets(3).Range("A:K").FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> wb.Worksheets(3).Range("A:K").Find(searchValue, LookIn:=xlValues, LookAt:=xlWhole).Address
End If
Application.CutCopyMode = False
wb.Close SaveChanges:=False
End Sub
Display More