Run-time error: '1004'

B

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
 
J

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
 
B

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Toolbar built for 2003 not loading in 2007 2
Toolbar Error 1
Run-time error: '9' 5
Command Button misfire 2
Run-time Error: '438' 2
Using wildcards in workbook calls 0
Worksheet selection 2
NEED HELP! Macro loop 7

Top