Insert and replace row

[vincentwady]

Dear All,

I have non experience in VBA, but I wish I can learn a lot from you.
In my work, I have a practice I need to create a VBA to improve repeat
procedure, and hope someone can help me.

I have 100 tab in my portfolio file, most of them have simular format,
but still a little different on the display row. This is the step I
need to do VBA:
1. I need to insert a new row above row 2 in all worksheet. Row 2
content " Price/Yield Report" text.
2. After insert new row above row 2, I need to do vlookup,
A2=vlookup(B4,'portfolio $A:$B,2,0)
3. rename the tab by the same name in A2(the vlookup result)
4. hide row from row 3 till 2 row above one specific cell content
"Given: Spread"
5. Done

Hope someone can give me a guide to start my first VBA.

Thank you so much

Vincent

 

[Guest]

A good start would be to turn on the macro recorder and perform the steps on
one sheet. (Tools=>Macro=>Record a New Macro , perform the same steps to
turn it off or click the stop recording button if it is visible - then look
at the recorded code in the VBE A+F11).

then you can put the recorded code inside a loop

for each sh in Worksheets
sh.Activate
' recorded code
Next

the only problem would be renaming the sheet.

Activesheet.Name = Range("B1").Value

Assuming B1 contains the name you want to use.

 

[vincentwady]

Thank you,

So should I do all the step in all the tab I need to change, or just
one?
But in my step 4, I have an issue, because it is not the same row I
need to hide in my all tab, how I should tell excel to do from row 3
to top 2 row on a specific row? This is because my format will
different based on security. Thank you so.

 

[Guest]

Do it to one sheet.

If I understood what

4. hide row from row 3 till 2 row above one specific cell content

"Given: Spread"

or

how I should tell excel to do from row 3

to top 2 row on a specific row? This is because my format will
different based on security.

actually means, I would be glad to tell you, but I don't understand it. Row
3 is a specific row. Top of row 2 - not sure what that is.

Perhaps something like

Sub Macro1()
Set rng = cells.Find(What:="Given: Spread", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
Range(rng,Rows(3)).EntireRow.Hidden = True
end if
End Sub

But I can't say that is what you want.

 

[vincentwady]

This is almost what I want, you are genius.
The meaning top 2 above specific row is my mistake, it should the 2
row above the specific row which content "Given:Spread". so the
result, I will still have Given:Spread show up, and also the original
one row above it show up.

 

[Guest]

or if Row 3 is above that and you want to hide down to there starting in row 3

Sub Macro1()
Set rng = cells.Find(What:="Given: Spread", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
Range(rng.Offset(-2,0),Rows(3)).EntireRow.Hidden = True
end if
End Sub

 

[Guest]

Sub Macro1()
Set rng = cells.Find(What:="Given: Spread", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng is nothing then
rng.offset(-2,0).EntireRow.Hidden = True
end if
End Sub

Then I would think.