heryyanto04
New Member
- Joined
- Sep 8, 2010
- Messages
- 1
Hi,
Can someone help me with the following macro:
- Can I simplified the following macro?
- I work with many sheets that have each of them have different name, such as: G 52535; G 52551; G 52612; etc.
So instead of write the sheet name I need one formula so it will automatically change when I make a copy and paste.
Thank you in advance for your help.
ActiveCell.FormulaR1C1 = "='G 52535'!R[30]C[-3]"
Range("M12").Select
ActiveCell.FormulaR1C1 = "='G 52551'!R[28]C[-3]"
Range("M14").Select
ActiveCell.FormulaR1C1 = "='G 52612'!R[26]C[-3]"
Range("M16").Select
The original macro is as follows:
Sub Macro1()
'
' Macro1 Macro
'
'
Range("C10:E16").Select
Selection.Replace What:="=", Replacement:="""=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Copy
Range("M10").Select
ActiveSheet.Paste
Range("A9").Select
Cells.Replace What:="""=", Replacement:="=", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range("M10").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "='G 52535'!R[30]C[-3]"
Range("M12").Select
ActiveCell.FormulaR1C1 = "='G 52551'!R[28]C[-3]"
Range("M14").Select
ActiveCell.FormulaR1C1 = "='G 52612'!R[26]C[-3]"
Range("M16").Select
End Sub
Can someone help me with the following macro:
- Can I simplified the following macro?
- I work with many sheets that have each of them have different name, such as: G 52535; G 52551; G 52612; etc.
So instead of write the sheet name I need one formula so it will automatically change when I make a copy and paste.
Thank you in advance for your help.
ActiveCell.FormulaR1C1 = "='G 52535'!R[30]C[-3]"
Range("M12").Select
ActiveCell.FormulaR1C1 = "='G 52551'!R[28]C[-3]"
Range("M14").Select
ActiveCell.FormulaR1C1 = "='G 52612'!R[26]C[-3]"
Range("M16").Select
The original macro is as follows:
Sub Macro1()
'
' Macro1 Macro
'
'
Range("C10:E16").Select
Selection.Replace What:="=", Replacement:="""=", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Copy
Range("M10").Select
ActiveSheet.Paste
Range("A9").Select
Cells.Replace What:="""=", Replacement:="=", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range("M10").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "='G 52535'!R[30]C[-3]"
Range("M12").Select
ActiveCell.FormulaR1C1 = "='G 52551'!R[28]C[-3]"
Range("M14").Select
ActiveCell.FormulaR1C1 = "='G 52612'!R[26]C[-3]"
Range("M16").Select
End Sub