PDA

View Full Version : Insert Row in new workbook



i_malc
06-15-2013, 03:42 AM
My latest problem is that from a command in workbook1 I'm trying to open an another workbook [book2] and inserted row in a new workbook and increment the formula in the cell above by 1. eg =A24+1 to =A25+1

Can anyone help?

xls
06-15-2013, 06:27 AM
Sub IncrBy1

Dim i as Long

Workbooks.Open Filename:= _
"C:\Test.xls"
Sheets("Sheet1").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row).Select
ActiveCell.EntireRow.Insert
F = ActiveCell.Offset(-1, 0).Formula
F2 = Mid(F, 2, WorksheetFunction.Find("+", F) - 2)
ActiveCell.Formula = "=" & Left(F2, 1) & _
Val(Mid(F2, 2, Len(F2))) - 1 & "+1"
End Sub


HTH//Thanks

i_malc
06-15-2013, 09:00 AM
I still got a problem when I run this it comes up with
"Run-Time error '1004'"
Select Method of Range class failed.


debug shows
Range("A" & Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row).Select

xls
06-15-2013, 10:28 PM
Sub IncrBy1
Dim i As Long

Workbooks.Open Filename:= _
"C:\Test.xls"
'enter sheet name, change sheet1 to your sheet name
Sheets("Sheet1").Select
Range("A" & Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row).Select
ActiveCell.EntireRow.Insert
F = ActiveCell.Offset(-1, 0).Formula
F2 = Mid(F, 2, WorksheetFunction.Find("+", F) - 2)
ActiveCell.Offset(-1, 0).Formula = "=" & Left(F2, 1) & _
Val(Mid(F2, 2, Len(F2))) - 1 & "+1"
End Sub


Try above..

Check your sheet name & change sheet1.xls with your sheet name..

HTH