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
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