Macro Merge Question

[GaryG9595]

Hello,
I recorded a macro that works perfectly, but is rather lengthy and I feel there is a shorter version that can be created. Also I would like to merge this with another macro that is currently the second step in my process.

I currently create a pivot table on a second sheet called "scorecard" from data on sheet 1 then I recorded this process.
Sub CreateSheet()
'
' CreateSheet Macro
'
'

Range("B3").Select
Selection.ShowDetail = True
Sheets("Scorecard").Select
Range("B4").Select
Selection.ShowDetail = True
Sheets("Scorecard").Select
Range("B5").Select
Selection.ShowDetail = True
Sheets("Scorecard").Select

ETC...

Range("B399").Select

Selection.ShowDetail = True

Sheets("Scorecard").Select

Range("B400").Select

Selection.ShowDetail = True

Sheets("Scorecard").Select

End Sub

<tbody>
</tbody><colgroup><col></colgroup>

Is there a way to shorten this to go ahead and click "B3,B4,B5, etc..... until it reaches an empty cell as I sometimes can have as many as 2000 or more sheets to create...?

And if possible can I combine the new macro with this one...?

This Macro obviously renames the new spreadsheets to my customers name in "B2".

Sub RenameTabs()
For i = 1 To Sheets.Count
If Worksheets(i).Range("B2").Value <> "" Then
Sheets(i).Name = Worksheets(i).Range("B2").Value
End If
Next
End Sub

Thanks in advance...
Gary

 

[par60056]

Welcome to the forum.

The macro recorder is very verbose.

Most things that you want to do with a macro should never change the selection.

You can trim a lot of the recorded code like this:

[COLOR=#0000FF]Range("B3").Select
Selection.ShowDetail = True
Sheets("Scorecard").Select
Range("B4").Select
Selection.ShowDetail = True
Sheets("Scorecard").Select
Range("B5").Select
Selection.ShowDetail = True
Sheets("Scorecard").Select[/COLOR]

Becomes

[COLOR=#0000FF]Sheets("Scorecard").[/COLOR][COLOR=#0000FF]Range("B3").ShowDetail = True
Sheets("Scorecard").Range("B4").ShowDetail = True
Sheets("Scorecard").Range("B5").ShowDetail = True
[/COLOR]

You might even be able to do it in 1 line. I do a lot of work with pivot tables but not in this way.

Sheets("Scorecard").Range("B3:B400").ShowDetail = True

If that doesn't work, you could write a loop:

dim currRow as Long
for currRow=3 to 400
    Sheets("Scorecard").Range("B"&currRow).ShowDetail = True
next

This could be modified to find the last cell or to use a While loop to increase currRow until it finds a blank.

People will do the same thing in many different ways. Knowing the starting point and what is the goal will often change the way you do something.

Good luck

 

[GaryG9595]

Thank you very much...... The loop function worked perfectly...

 

[par60056]

Glad that helped.

The macro recorder is a good way to learn about the different object and how you can work with them.

But it is VERY literal. If you scroll the screen to do something while recording, the macro will have the screen scroll. It tends to "select" everything and then work on the selection. You can usually remove those. Very rarely do my macros change the selection and depending on the macro I usually store the selection in a range before I change it and restore it when I'm done.