partial sheet name activating sheet

[jordanburch]

Hey guys,

I have the below. When it says sheets("Clearedsheet").activate it gives me an object not defined error. It seems to work on other files just this one not sure why its erroring out.

Sub LoopThroughFiles()
Dim MyObj As Object
Dim MySource As Object
Dim file As Variant
Dim wbThis                  As Workbook     'workbook where the data is to be pasted, aka Master file
Dim wbTarget                As Workbook     'workbook from where the data is to be copied from, aka Overnights file
Dim LastRow As Long
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'set to the current active workbook (the source book, the Master!)
Set wbThis = ActiveWorkbook
   
Set sht1 = wbThis.Sheets("Sheet1")

Folder = "C:\Users\jordan.burch.ctr\Desktop\Combine files\"
Fname = Dir(Folder)

While (Fname <> "")

  Set wbTarget = Workbooks.Open(FileName:=Folder & Fname)
  wbTarget.Activate
  Application.DisplayAlerts = False
Application.ScreenUpdating = False

  Dim ws As Worksheet
    Dim ClearedSheet As String
    ClearedSheet = ""
    For Each ws In ActiveWorkbook.Worksheets
        If InStr(1, ws.Name, "ER", vbTextCompare) Then
            ClearedSheet = ws.Name
           Sheets(ClearedSheet).Activate
            With ActiveSheet
      If .AutoFilterMode Then
        If .FilterMode Then
            .ShowAllData
        End If
    Else
        If .FilterMode Then
            .ShowAllData
        End If
    End If
    End With

  
            Exit For
        End If
        Application.DisplayAlerts = False
Application.screeupdating = False
    Next
   If ClearedSheet <> "" Then


  Range("a2:T40000").Copy

  wbThis.Activate

 'Just add this line:
  LastRow = sht1.Range("b1").End(xlDown).Row + 1
 'And alter this one as follows:
  sht1.Range("a" & Range("A" & Rows.Count).End(xlUp).Row + 1).PasteSpecial

 Fname = Dir

 'close the overnight's file
  wbTarget.Close
End If
Wend
 
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Any thoughts?

Jordan

 

[Marc L]

Hi,​

​

as this variable is useless so directly use Ws.Activate but hoping the sheet is visible rather than hidden …​

​

And just using this worksheet variable activating it is so useless too !​

 

[jordanburch]

Hi,​

​

as ClearedSheet is not a sheet name but just a variable so obviously remove the double quotes …​

Hi Marc,
Which ones do I remove?

Jordan

 

[Marc L]

​

In fact I just change my mind 'cause your post was not well reflecting your code so read my last version of my previous post, thanks.​