Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
insert a new row in VB
Hi, I was wondering if anyone might be able to help me here. How do i insert a new row in a worksheet in excel using VB programming? For example, in a worksheet called Data, the following is the data A B C 1 Merchandise 500 600 2 02-000 200 200 3 02-001 300 600 4 Total 1000 1400 5 Grocery 200 80 6 03-001 200 200 7 ... I'd like to be able to insert a new row after "Total" which is after line no. 4, just to separate them between merchandise and grocery (formatting issue). I've got some codes but not working really yet (got some errors) If insertnewline = True Then Worksheets("Data").Range("A4").Select Selection.Insert Shift:=xlDown end if The error message that i got is run-time error '1004' select method of range class failed I don't know why it doesn't like the range. Your help is greatly appreciated. Thank you in advance -- associates ------------------------------------------------------------------------ associates's Profile: http://www.excelforum.com/member.php...o&userid=35073 View this thread: http://www.excelforum.com/showthread...hreadid=548249 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
insert a new row in VB
Hi, you will get that error if the worksheet is protected. If this is the case then your code has to unprotect the sheet at the start, then reapply protection at the end. Ken Johnson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
insert a new row in VB
Hi again, Also, your code only inserts a cell in column A. If you want columns B and C to be similarly affected then try... If insertnewline = True Then With Worksheets("Data") .Unprotect (password) 'if protected without password leave the brackets and contents .Range("A4:C4").insert Shift:= xlDown .Protect End With End if or if you want to insert an entire row across the sheet then try... If insertnewline = True Then With Worksheets("Data") .Unprotect (password) 'if protected without password leave the brackets and contents .Range("A4").EntireRow.Insert .Protect End With End if Ken Johnson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
insert a new row in VB
Hi again,again I'd better clear up my mistake with the password stuff. If the sheet is protected without a password then your code only needs to say .Unprotect then .Protect If a password such as abc was used then the code should be .Unprotect (abc) then ..Protect (abc) Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I insert a reference to lookup and insert a name | New Users to Excel | |||
insert picture | Excel Discussion (Misc queries) | |||
Insert Line Macro | Excel Discussion (Misc queries) | |||
Challenging Charting | Charts and Charting in Excel | |||
Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions |