New Excel macro giving Runtime error 438 Object doesnt Support this property or method 1

[vba317]

I am creating a new Macro and I getting an error 438. I have tried to create the object but it is still not working. The name of the first worksheet is AMDOCS_DATA. The line highlighted in Blue is what is causing the error.Any help would be appreciated. Tom

Sub Formatting()

Dim rng As Range
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

'TAB AMDOCS_DATA
ThisWorkbook.Worksheets("AMDOCS_DATA").Select
[BlUE]For Each rng In xlBook.AMDOCS_DATA.Range("H:R").Columns [/BLUE]
   rng.TextToColumns
Next rng

'TAB CSG_DATA
For Each rng In CSG_DATA.Range("C:Q").Columns
   rng.TextToColumns
Next rng

'TAB AM_DAYS_OUT

For Each rng In AM_DAYS_OUT.Range("D:M").Columns
   rng.TextToColumns
Next rng

'TAB CSG_DAYS_OUT
For Each rng In CSG_DAYS_OUT.Range("D:M").Columns
   rng.TextToColumns
Next rng

'Delete function

    'Delete Columns B-G in worksheet AMSDOCS_DATA
    ThisWorkbook.Worksheets("AMDOCS_DATA").Columns("B:G").Delete
    'Delete Column A in Worksheet CSG_DATA
    ThisWorkbook.Worksheets("CSG_DATA").Columns("A").Delete
    'Delete Row 1 in Worksheet CSG_DATA
    ThisWorkbook.Sheets("CSG_DATA").Range("1").Delete xlUp
    
End Sub

 

[combo]

You try to refer to worksheet by its code name, if it's what you intedd, use:
[tt]For Each rng In AMDOCS_DATA.Range("H:R").Columns[/tt]
This will work if this sheet is in the same workbook where the calling code is, otherwise no. Actually, your code tries to access newly created workbook (you have [tt]Set xlBook = xlApp.Workbooks.Add[/tt] and next [tt]For Each rng In xlBook.AMDOCS_DATA.Range("H:R").Columns[/tt]).


combo

 

[vba317]

Thanks,
I took out the xlbook before the AMDOCS data and it works fine now. I don't want to add a new workbook. How could I change the code so the code refers to the current Workbook?

Tom

 

[combo]

What do you mean by "current workbook? Workbook with the calling code: ThisWorkbook. Currently active workbook: ActiveWorkbook. Open workbook with specific name: Workbooks("FileName").

combo

 

[SkipVought]

Are you not coding in Excel VBA?

So why are you creating a new instance if the Excel Application Object?