Set the copy to column to a variable
Hi Claus,
I found my mistakes, seeks I was using improper sheet naming conventions with in this With statement. I was not including Sheets(MySheet). where needed.
Seems to work fine now.
With Sheets(MySheet)
myFirst = Sheets(MySheet).Columns("A").Column
myLast = Sheets(MySheet).Columns("C").Column
ReDim Preserve varRows(myLast - myFirst)
' Finds the row number of the longest column between A and C
For i = myFirst To myLast
varRows(i - myFirst) = Sheets(MySheet).Cells(Rows.Count, i).End(xlUp).Row
Next
Set aScan = Sheets(MySheet).Range("A3:C" & Application.Max(varRows)).Find(cScan, LookAt:=xlWhole)
If Not aScan Is Nothing Then
myCol = Application.Match(MySheet, Sheets("FBAout").Range("1:1"), 0)
ActiveWindow.ScrollColumn = myCol
Set dest = Sheets("FBAout").Cells(Rows.Count, myCol).End(xlUp)(2)
dest.Offset(0, 1) = Date
aScan.Cut dest
Else
MsgBox " No match found."
End If
End With
Howard
|