I have working code if i change the Last Column find for a number above my last bit of data.
The last column code (lc) works up to row 5 of the Quotes sheet but if a run quote 6 or higher it thinks the last column with data is the last one in the section of data before the loop.
Still runs the first bit though.
Basically this code copies data from a row on Sheet "Quotes" and places it back onto a Sheet "order Template" in the right places. But should stop at the last piece of data (lc) in the Row QuoteREF.
Any ideas? Thanks
The last column code (lc) works up to row 5 of the Quotes sheet but if a run quote 6 or higher it thinks the last column with data is the last one in the section of data before the loop.
Still runs the first bit though.
Basically this code copies data from a row on Sheet "Quotes" and places it back onto a Sheet "order Template" in the right places. But should stop at the last piece of data (lc) in the Row QuoteREF.
Any ideas? Thanks
VBA Code:
Sub Quotedatapull_v2()
Dim QuoteREF As Range
Dim Cols As Variant
Dim i As Long, j As Long, k As Long, n As Long
Dim lc As Long
Cols = Split("1 4 6 9 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33")
Set QuoteREF = Sheets("Quotes").Range("A:A").Find(Sheets("order template").Range("F8").Value)
If Not QuoteREF Is Nothing Then
Application.ScreenUpdating = False
ThisWorkbook.ClearForm
Sheets("Order Template").Range("A2").Value = QuoteREF.Offset(, 0)
Sheets("Order Template").Range("F12").Value = QuoteREF.Offset(, 1)
Sheets("Order Template").Range("C2").Value = QuoteREF.Offset(, 2)
Sheets("Order Template").Range("B26").Value = QuoteREF.Offset(, 3)
Sheets("Order Template").Range("B4").Value = QuoteREF.Offset(, 4)
Sheets("Order Template").Range("B6").Value = QuoteREF.Offset(, 5)
Sheets("Order Template").Range("B8").Value = QuoteREF.Offset(, 6)
Sheets("Order Template").Range("B10").Value = QuoteREF.Offset(, 7)
Sheets("Order Template").Range("B12").Value = QuoteREF.Offset(, 8)
Sheets("Order Template").Range("B14").Value = QuoteREF.Offset(, 9)
Sheets("Order Template").Range("B15").Value = QuoteREF.Offset(, 10)
Sheets("Order Template").Range("B16").Value = QuoteREF.Offset(, 11)
Sheets("Order Template").Range("B17").Value = QuoteREF.Offset(, 12)
Sheets("Order Template").Range("B18").Value = QuoteREF.Offset(, 13)
Sheets("Order Template").Range("B20").Value = QuoteREF.Offset(, 14)
Sheets("Order Template").Range("B21").Value = QuoteREF.Offset(, 15)
Sheets("Order Template").Range("B22").Value = QuoteREF.Offset(, 16)
Sheets("Order Template").Range("B23").Value = QuoteREF.Offset(, 17)
Sheets("Order Template").Range("B24").Value = QuoteREF.Offset(, 18)
Sheets("Order Template").Range("E2").Value = QuoteREF.Offset(, 19)
Sheets("Order Template").Range("F2").Value = QuoteREF.Offset(, 20)
With Sheets("order template").Range("A31")
k = 1
lc = Cells(QuoteREF.Rows, Columns.Count).End(xlToLeft).Column
For i = 21 To lc
.Cells(k, Val(Cols(j))).Value = QuoteREF.Offset(, i).Value
j = j + 1
If j > UBound(Cols) Then
j = 0
k = k + 1
End If
Next i
End With
Application.ScreenUpdating = True
Else
MsgBox ("Quote Not Found")
End If
End Sub