Got the following code below that I had planned to use to copy some data from one tab (comes in tab like jan 2011.txt, etc.) to another, based off of multiple finds of a few identifiers, getting the column and then doing my copy n paste. With just one identifier it works fine, without the copy n paste i could get my msgbox to give the right column with multiple finds, together no joy- giving the Error 91 (Object variable not set), have tried looking thro and understanding the help files but to no avail, would appreciate a helping hand?
Code:
Sub Copy_Data_Test_Two()
Dim ws As Worksheet, N As String, wsN As Worksheet
Dim Found As Range, lRowEnd As Integer
For Each ws In ThisWorkbook.Worksheets
If Right(ws.Name, 4) = ".txt" Then
N = "TEST ONLY- " & Left(ws.Name, Len(ws.Name) - 4)
If SheetExists(N) = False Then Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = N
Set wsN = ActiveWorkbook.Sheets(N)
Set Found = Columns("AA:AZ").Find(what:="SL", LookIn:=xlValues, lookat:=xlPart)
If Found Is Nothing Then Set Found = Columns("AA:AZ").Find(what:="CA", LookIn:=xlValues, lookat:=xlPart)
If Found Is Nothing Then Set Found = Columns("AA:AZ").Find(what:="PT", LookIn:=xlValues, lookat:=xlPart)
If Found Is Nothing Then Set Found = Columns("AA:AZ").Find(what:="SZ", LookIn:=xlValues, lookat:=xlPart)
'MsgBox "found is in: " & Found.Column
lRowEnd = ws.Cells(Rows.Count, Found.Column).End(xlUp).Row
ws.Select
ws.Range(Cells(1, Found.Column - 3), Cells(lRowEnd, Found.Column)).Copy
wsN.Select
ActiveSheet.Paste Destination:=Worksheets(N).Range("A2")
End If
Next ws
End Sub