Run-time error: '1004'

[Bishop]

I'm getting a 1004 runtime error for the following line of code:

CDLastRow = Workbooks("Test Tally SheetII").Worksheets("Catalyst
Dump").Range("A" & Rows.Count).End(xlUp).Row

I'm trying to get the sub routine to work no matter what sheet/workbook I'm
in. Here is the complete code:

Sub CatalystToTally()

Dim wb As Workbook
Dim ws As Worksheet
Dim CDLastRow As Integer 'Catalyst Dump
Dim EDLastRow As Integer 'Exported Data

CDLastRow = Workbooks("Test Tally SheetII").Worksheets("Catalyst
Dump").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Catalyst Dump").Columns("D").ColumnWidth = 13

For Each wb In Workbooks
'Test to see if wb's name is like "ExportedData*"
If wb.Name Like "ExportedData*" Then
'Create a worksheet object to reference the appropriate
'worksheet in the wb
Set ws = wb.ActiveSheet

With ws
.Rows("1:1").Delete Shift:=xlUp
EDLastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Columns("D").ColumnWidth = 13
.Columns("D").NumberFormat = "0"
.Rows("1:" & EDLastRow).Copy ThisWorkbook.Worksheets _
("Catalyst Dump").Rows(CDLastRow + 1)
End With
wb.Close savechanges:=False
End If
Next
End Sub

 

[Jacob Skaria]

Try the below...... (.xls)

Dim CDLastRow As Long 'Catalyst Dump
Dim EDLastRow As Long 'Exported Data

CDLastRow = Workbooks("Test Tally SheetII.xls").Worksheets _
("Catalyst Dump").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Catalyst Dump").Columns("D").ColumnWidth = 13

If this post helps click Yes

 

[Jacob Skaria]

Try the below...... (.xls)

Dim CDLastRow As Long 'Catalyst Dump
Dim EDLastRow As Long 'Exported Data

CDLastRow = Workbooks("Test Tally SheetII.xls").Worksheets _
("Catalyst Dump").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Catalyst Dump").Columns("D").ColumnWidth = 13

If this post helps click Yes

 

[Bishop]

Still giving me an Run-time error: '9' Subscript out of range. I changed it
per your instruction:

Dim CDLastRow As Long 'Catalyst Dump
Dim EDLastRow As Long 'Exported Data

CDLastRow = Workbooks("Test Tally SheetII.xlsm").Worksheets _
("Catalyst Dump").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Catalyst Dump").Columns("D").ColumnWidth = 13

Since I'm working in 07 and my sheet is saved as .xlsm I used that instead
of .xls. But it locks up on this line still.

 

[Bishop]

Still giving me an Run-time error: '9' Subscript out of range. I changed it
per your instruction:

Dim CDLastRow As Long 'Catalyst Dump
Dim EDLastRow As Long 'Exported Data

CDLastRow = Workbooks("Test Tally SheetII.xlsm").Worksheets _
("Catalyst Dump").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("Catalyst Dump").Columns("D").ColumnWidth = 13

Since I'm working in 07 and my sheet is saved as .xlsm I used that instead
of .xls. But it locks up on this line still.