ExcelBanter - View Single Post - Excel macro

Victor Delta[_2_]

In article , lid says...

Garry

Many thanks for all your efforts. I can supply the other macros code
but
what really puzzles me is why this is becoming so very complicated?

Well it's a rather simple task IMO, but I suspect that (as you state
below) your limited knowledge of VB is the major issue. Post your other
macro code -OR- upload your file to a public share and post a download
link so I can edit it and return to you.

At present, I simply go (manually) to the appropriate cells in Col G
(starting with G3) and invoke the Copy_Title macro using the shortcut
Ctrl + T. Having now created another macro which can go to the cells
in
question, why is it not possible to make it similarly run the macro
in
each cell?

I suspect your code operates on the premise the target cell is the
active cell. Since your loop changes the target cell each iteration,
your code needs to ref the next cell to act on.

My code requires no cells be selected as doing so generally results
inefficient performance.

Sorry if this is a stupid question but, as you can tell, my knowledge
of
VB is fairly limited!

Thanks for your patience.

Always glad to help...

By the way, I totally agree with you about running the loop to row 3
(ie
cell G3) but I find that the macro still also operates on the cell in
which the cursor starts - whatever row or column it's in. Hence my
probably rather heavy-handed "Range("G3").Select" to try and overcome
this problem.

This absolutely does not happen with my test data, using my code 'as
posted'.

V

Garry

Many thanks. Here's the 'suite' of Copy_Title macros:

Sub Copy_Cat_Name()
'
' Copy_Cat_Name Macro
' Macro recorded 16/01/2012

ActiveCell.Select
ActiveCell.FormulaR1C1 = "=R[1]C[-5]"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
If ActiveCell.Value = "zOther Activities" Then
ActiveCell.Value = "Other Activities"
End If
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
ActiveCell.Resize(1, 7).Select
Selection.Merge
With Selection
.HorizontalAlignment = xlLeft
End With
With Selection.Font
.Name = "Calibri"
.Size = 11
End With
End Sub

Sub Copy_Day_Name()
'
' Copy_Day_Name Macro
' Macro recorded 16/01/2012
'

ActiveCell.Select
ActiveCell.FormulaR1C1 = "=R[1]C[0]"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
If ActiveCell.Value = "Monthly" Then
ActiveCell.Value = "Other Activities"
End If
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
ActiveCell.Resize(1, 7).Select
Selection.Merge
With Selection
.HorizontalAlignment = xlLeft
End With
With Selection.Font
.Name = "Calibri"
.Size = 11
End With
End Sub

Sub Copy_Loc_Name()
'
' Copy_Loc_Name Macro
' Macro recorded 16/01/2012
'

ActiveCell.Select
ActiveCell.FormulaR1C1 = "=R[1]C[8]"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
If ActiveCell.Value = "zy" Then
ActiveCell.Value = "Other Activities"
End If
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
ActiveCell.Resize(1, 6).Select
Selection.Merge
With Selection
.HorizontalAlignment = xlLeft
End With
With Selection.Font
.Name = "Calibri"
.Size = 11
End With
End Sub

Sub Copy_Title()
' shortcut ctrl+t
' Invokes appropriate copy/format macro depending on worksheet

Dim s As String
s = ActiveSheet.Name
If s = "By Category" Then Call Copy_Cat_Name
If s = "By Day" Then Call Copy_Day_Name
If s = "By Location" Then Call Copy_Loc_Name

End Sub

Hope you can make sense of them!

Thanks again

V