I'm trying to work on some code that will
1. allow me to find things in worksheet1 which are "sold",
2. copy the three cells immediately to the right of that cell
3. Insert copied cells into worksheet2 but starting at a specific point
4. Copied cells must be copied as text/value as the cells contain formula
The "Sold" status will only be in one column (L)
I managed at one point to copy some over but I only got the formulas, but now I'm getting errors and I'm lost
Any help would be greatly appreciated
1. allow me to find things in worksheet1 which are "sold",
2. copy the three cells immediately to the right of that cell
3. Insert copied cells into worksheet2 but starting at a specific point
4. Copied cells must be copied as text/value as the cells contain formula
The "Sold" status will only be in one column (L)
I managed at one point to copy some over but I only got the formulas, but now I'm getting errors and I'm lost
Any help would be greatly appreciated
Code:
Sub CommandButton1_Click()
Dim C As Range
Dim rngCopyRange As Range
Dim FirstAddress As String
Dim shtSheet1 As Worksheet
Dim shtSheet2 As Worksheet
Dim lngSheet2LastRow As Long
' Find All Values And Copy the three Cells To the
' right To sheet 2 at a specific point in the second worksheet
End Sub
Public Sub FindSettledinQtr()
Set shtSheet1 = Sheets("Vehicle Data")
Set shtSheet2 = Sheets("Statement")
' Assume Column L Always Has Data
lngSheet2LastRow = shtSheet2.Cells(Rows.Count, "L").End(xlUp).Row
With shtSheet1.Range("A1:P999")
Set C = .Find("Paid", LookIn:=xlValues)
If Not C Is Nothing Then
FirstAddress = C.Address
Call CopyData
Set C = .FindNext(C)
Do While Not C Is Nothing And C.Address <> FirstAddress
Call CopyData
Set C = .FindNext(C)
Loop
End If
End With
End Sub
Public Sub CopyData()
lngSheet2LastRow = lngSheet2LastRow + 1
Set rngCopyRange = Range(C, C.Offset(0, 3))
rngCopyRange.Copy shtSheet2.Cells(lngSheet2LastRow, 1)
End Sub
Last edited: