Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run-time error: '1004'
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run-time error: '1004'
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 "Bishop" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run-time error: '1004'
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. "Jacob Skaria" wrote: 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 "Bishop" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error 1004... | Excel Discussion (Misc queries) | |||
Run-time error '1004' | Excel Discussion (Misc queries) | |||
Run Time Error 1004 | Excel Worksheet Functions | |||
Run-time error '1004': | Excel Discussion (Misc queries) | |||
Run time error 1004, General ODBC error | New Users to Excel |