ActiveCell other sheet

[stella3000]

I’ve created the following macro (relative reference) and assigned it to a button in order to be applied on the first sheet (= “Jan”) :<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Sub IP_III()
'
' IP_III Macro
' Macro recorded 18/08/2009 by brutsaj
'
'
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
Selection.Insert Shift:=xlDown
ActiveCell.Offset(0, 3).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "n"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "n"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "n"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "n"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "n"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "0"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "0"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "0"
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.Offset(0, -127).Range("A1:DI1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.Font.Italic = True
ActiveCell.FormulaR1C1 = "Insert Post Name"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

I have a workbook that contains multiple sheets (for each month the same one) with exactly the same content. I want this macro to automatically be applied to the other sheets on the exact same location of the active cell on the first sheet.


How do I translate this to VBA code?<o></o>

 

[Norie]

A worksheet doesn't have an ActiveCell property.

What do you actually want to do with this code?

You might be able to replace the first part of the code with this.

'
With ActiveCell.Rows("1:1").EntireRow
    .Copy
    .Insert Shift:=xlDown
End With
ActiveCell.Offset(0, 3) = "n"
ActiveCell.Offset(0, 4) = "n"
ActiveCell.Offset(0, 5) = "n"
ActiveCell.Offset(0, 6) = "n"
ActiveCell.Offset(0, 7) = "n"
ActiveCell.Offset(0, 11) = "0"
ActiveCell.Offset(0, 15) = "0"
ActiveCell.Offset(0, 19) = "0"

Note I said 'might' and haven't dealt with the rest of the code.

The reason for that is it's pretty hard to follow what the code is actually meant to do with all the ActiveCell, Offset, Select etc.

 

[stella3000]

Thank you for your reply, Norie!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
But just ignore my macro from above. If you have 12 exactly the same sheets with exactly the same content and you want to create a macro that does the same actions (copy, insert, ...) in the active sheet, scope = based on the active/selected row, to the other 11 sheets...How would you do that? <o></o>
<o></o>
I just have trouble to create this macro because I don't have an absolute address from a cell to refer to because it's variable/ it depends on what cell is active. The actions aren't variable so these I can program, but not the location on which the actions should be applied on.<o></o>
<o></o>
Man, it’s difficult to explain this!

 

[Norie]

Well if you want to cycle through all the worksheets in a workbook you could use something like this.

For Each ws In ThisWorkbook.Worksheets
     ws.Range("A1") = ws.Name
     ' all sorts of other code here
Next ws