|
how do i write a macro that will add rows into a spreadsheet
im trying to add 15 rows in between each row in a spreadsheet
all of which contain information i would like to use as headers i have approximately 1600 rows in this worksheet im assuming a macro may be the easiest way to do this but am open to any suggestions that any of you may have thank you for your assistance brock |
how do i write a macro that will add rows into a spreadsheet
Hi BROCK:
This should work if you paste it into a module. If you need any further help, please let me know. Thanks, Simon Sub Row_Adder() Dim curr_row, no_insert, start_row, exec_loop, offset, ins_stop, i As Integer ' Set the row you want the code to start running from start_row = 20 'Set the number of rows you want to insert no_insert = 15 'Set the number of times you want this code to run exec_loop = 20 'Set the number of rows you want to offset each time. eg: if you 'want to insert 10 rows then move down 5 rows before inserting 'the next ten offset = 2 'Move to the start Rows(start_row).Select For i = 1 To exec_loop curr_row = ActiveCell.Row ins_stop = curr_row + no_insert 'Select and insert the rows Rows(curr_row & ":" & ins_stop).Select Selection.Insert Shift:=xlDown 'select the bottom of your insert Rows(ins_stop).Select 'move to the offset Rows(ActiveCell.Row + offset).Select Next i End Sub BROCK8292 wrote: im trying to add 15 rows in between each row in a spreadsheet all of which contain information i would like to use as headers i have approximately 1600 rows in this worksheet im assuming a macro may be the easiest way to do this but am open to any suggestions that any of you may have thank you for your assistance brock -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200704/1 |
how do i write a macro that will add rows into a spreadsheet
thank you for your help smw226
the language you provided worked perfectly so perfectly in fact that i was wondering if i might push my luck my workbook contains two worksheets worksheet 1 has 1600 rows of information worksheet 2 has basically the same info but each row that contains that info is now seperated by 15 lines thanx to you is there a way to insert hyperlinks into column A of worksheet 1 that will link each row to its corresponding row in worksheet 2? any advice on this matter will be greatly appreciated and thank you for the time youve already spent on me brock "smw226 via OfficeKB.com" wrote: Hi BROCK: This should work if you paste it into a module. If you need any further help, please let me know. Thanks, Simon Sub Row_Adder() Dim curr_row, no_insert, start_row, exec_loop, offset, ins_stop, i As Integer ' Set the row you want the code to start running from start_row = 20 'Set the number of rows you want to insert no_insert = 15 'Set the number of times you want this code to run exec_loop = 20 'Set the number of rows you want to offset each time. eg: if you 'want to insert 10 rows then move down 5 rows before inserting 'the next ten offset = 2 'Move to the start Rows(start_row).Select For i = 1 To exec_loop curr_row = ActiveCell.Row ins_stop = curr_row + no_insert 'Select and insert the rows Rows(curr_row & ":" & ins_stop).Select Selection.Insert Shift:=xlDown 'select the bottom of your insert Rows(ins_stop).Select 'move to the offset Rows(ActiveCell.Row + offset).Select Next i End Sub BROCK8292 wrote: im trying to add 15 rows in between each row in a spreadsheet all of which contain information i would like to use as headers i have approximately 1600 rows in this worksheet im assuming a macro may be the easiest way to do this but am open to any suggestions that any of you may have thank you for your assistance brock -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200704/1 |
how do i write a macro that will add rows into a spreadsheet
Hi Brock,
Just to confirm my uderstanding: Do you want, for example, the first hyperlink in sheet!A1 to reference Sheet2! A1, then Sheet!A2 to reference Sheet2!A16 etc ? Thanks, Simon BROCK8292 wrote: thank you for your help smw226 the language you provided worked perfectly so perfectly in fact that i was wondering if i might push my luck my workbook contains two worksheets worksheet 1 has 1600 rows of information worksheet 2 has basically the same info but each row that contains that info is now seperated by 15 lines thanx to you is there a way to insert hyperlinks into column A of worksheet 1 that will link each row to its corresponding row in worksheet 2? any advice on this matter will be greatly appreciated and thank you for the time youve already spent on me brock Hi BROCK: [quoted text clipped - 55 lines] thank you for your assistance brock -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200704/1 |
how do i write a macro that will add rows into a spreadsheet
yes simon,
thats exactly what im trying to do brock "smw226 via OfficeKB.com" wrote: Hi Brock, Just to confirm my uderstanding: Do you want, for example, the first hyperlink in sheet!A1 to reference Sheet2! A1, then Sheet!A2 to reference Sheet2!A16 etc ? Thanks, Simon BROCK8292 wrote: thank you for your help smw226 the language you provided worked perfectly so perfectly in fact that i was wondering if i might push my luck my workbook contains two worksheets worksheet 1 has 1600 rows of information worksheet 2 has basically the same info but each row that contains that info is now seperated by 15 lines thanx to you is there a way to insert hyperlinks into column A of worksheet 1 that will link each row to its corresponding row in worksheet 2? any advice on this matter will be greatly appreciated and thank you for the time youve already spent on me brock Hi BROCK: [quoted text clipped - 55 lines] thank you for your assistance brock -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200704/1 |
how do i write a macro that will add rows into a spreadsheet
thank you again for the time and attention youre giving me
youre understanding is 100% accurate that is exactly what im trying to do brock "smw226 via OfficeKB.com" wrote: Hi Brock, Just to confirm my uderstanding: Do you want, for example, the first hyperlink in sheet!A1 to reference Sheet2! A1, then Sheet!A2 to reference Sheet2!A16 etc ? Thanks, Simon BROCK8292 wrote: thank you for your help smw226 the language you provided worked perfectly so perfectly in fact that i was wondering if i might push my luck my workbook contains two worksheets worksheet 1 has 1600 rows of information worksheet 2 has basically the same info but each row that contains that info is now seperated by 15 lines thanx to you is there a way to insert hyperlinks into column A of worksheet 1 that will link each row to its corresponding row in worksheet 2? any advice on this matter will be greatly appreciated and thank you for the time youve already spent on me brock Hi BROCK: [quoted text clipped - 55 lines] thank you for your assistance brock -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200704/1 |
| All times are GMT +1. The time now is 08:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com