Help writing a macro

[IcarusFB]

Hi all! I'm very new to macros (I just discovered them tonight! lol)
and I am having some problems:

I have 12 sheets (Jan-Dec) on the project I am working on and each page
has stats on 28 staff members in my department. I am trying to insert 2
rows for each staff member on each sheet. That works out to 672 rows
that I have to add, which is very time consuming. Here is the macro I
have so far:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 31/12/2005 by John Smith
'
' Keyboard Shortcut: Ctrl+t
'
Range("B11:AJ11").Select
Selection.EntireRow.Insert
Selection.EntireRow.Insert

End Sub

For the range, the numbers would change as follows: B21:AJ21, B29:AJ29,
B37:AJ37, etc....

Is it possible to write all of this under one macro or do I have to
write a different macro for each row?

Thanks for your time!

(I have a feeling its going to be quicker to add the rows manually).

 

[Bob Phillips]

Not absolutely sure about the row gaps, but let us assume that you want
every 8th row from row 11 to row 235 (you can always adjust to suit) then
something like

Sub AddRows()
Dim sh As Worksheet
Dim i As Long

For Each sh In Worksheets(Array("Jan","Feb","Mar")) ' add rest yourself
For i = 235 to 11 Step -8
rows(i).Resize(2).Insert
Next i
Next sh
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[gn59]

Here is a slight variation on Bob's solution.
I assume you have each sheet populated with staff members names.
I also assume the name are in col A or Col B startin at row 11 and
ending in row 39 (11+ 28).
The code below selects all of the sheets at once and then performs the
insertion of 2 rows beneath each staff member.

Sub AddRows()

Sheets(Array("Jan", "Feb", "Mar", "Apr")).Select '<== Add additional
sheets

For i = 12 To 96 Step 3
Worksheets(1).Rows(i).Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Next i

End Sub

 

[Bob Phillips]

Yeah, selecting them all is a good idea. I like it <vbg>

Bob

 

[Cutter]

Just for my own understanding (I'm also trying to learn VBA) - what is
the purpose of the line

Application.CutCopyMode = False

in this code?

I have used it after pasting something to cancel the "marching ants"
but I don't understand what it does in this code.

 

[Chip Pearson]

It clears Excel objects from the Windows clipboard.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Cutter" <[email protected]>
wrote in message
news:[email protected]...

 

[IcarusFB]

I ran the macro but it isn't consistent throughout each sheet. One
column of stats will have 2 rows added and another will have 8. I need
the 2 rows added to every 8th line (just like it says in the code) but
for some reason it isn't giving me an even distribution of rows.

If I know exactly which lines I need the rows on, how would I rewrite
the code, to tell the macro to add 2 rows to each line listed in the
code?

For example: "add 2 rows to line 11, add 2 rows to line 19, add 2 rows
to line 27, etc...."

I'm assuming that this have to be re-written somehow:

For i = 227 To 8 Step -8
Rows(i).Resize(2).Insert

 

[IcarusFB]

Thanks for all your help folks but I don't think I'm ready (or patient
enough) to try and figure all of this out. What I'm going to do is set
up one sheet the way I want it and then copy and paste it overtop of the
other sheets that have the same number of days in the month then just
use the replace function to rename the months.

With all your help and suggestions, at least I'll be a leg up when I
finally sign up for my Excel course.

 

[Tom Ogilvy]

Sub AddRows()
Dim sh As Worksheet
Dim i As Long
v = Array("Jan","Feb","Mar","Apr","May","Jun", _
"Jul","Aug","Sep","Oct","Nov","Dec")
worksheets(v).Select
For i = 229 to 21 Step -8
rows(i).Resize(2).Insert
Next i
i = 11
rows(i).Resize(2).Insert
End Sub