One Macro for Copy/Paste, Formula, Colouring and Hyperlinking for at home

[BrianD]

Hey


I tried for hours/days/weeks/weekends to handcraft a VBA / Macro for my project, spend many hours on searching forums for answers
but VBA / Macro is so sensitive to a mistake that it will not work at all, plus i have no VBA skills :(. I m using EXCEL version 16.57 for MAC from Microsoft 365. My Goal is to reach one Macro for my steps, if that is possible


So, Macro should copy cells in Sheet 1 G3, G4, H4 to Sheet 2 EZ3, EZ4 and EZ5, the next step would be Macro must fill text in Sheet 2 EZ13, font arial size 16 I know how to do both steps with one Macro Record, i would need help for the upcoming step, next step Macro must change color for Sheet 2 EZ13 depending if the number in sheet 2 EZ3 is above zero to RGB ( 0, 135, 60) or below zero to RGB (235, 15, 41), this case red, this step i found no help with Macro Records, then next step Macro must hyperlink Sheet 2 EZ13 to Sheet 3 EZ13, then next step Macro must fill in Sheet 3 EZ13 with the same text, colour, font arial and font size 16 as from Sheet 2 EZ13, then next step Macro must hyperlink sheet 3 EZ13 to sheet 2 EZ13, there i could use on Macro Record the function copy / paste and the hyperlink. I know how to do that with Macro Record but don t know how to add it and make it work with previous steps plus i m missing one complete step




Is there hope for me, can u guys give me some help please ?

[SamT]

Each "Macro" or VBA Procedure, (Sub or Function,) should only perform one task (or only closely related tasks.)


Sub Macro1()
does this
End Sub

Sub Macro2()
does other
End Sub

Sub Macro3()
Does something else
End sub


Sub Main()
Dim and Set Sh1, Sh2, Sh3, Color1, Color2
' Control all variables here so maintenance of code is easier: Recommended, not required
' Pass Variables to subsequent Subs, ("Macros".)
'Copy Cells
Macro1 Sh1, Sh2

'Change Colors
Macro2 Color1

'Do something else
Macro3 Color2

' Do more stuff
Etc.

End Sub

[BrianD]

[QUOTE=SamT;413355]Each "Macro" or VBA Procedure, (Sub or Function,) should only perform one task (or only closely related tasks.)


Sub Macro1 Copy from Sheet 1 to Sheet 2
Sheets("Sheet1").Select Range("G3:G4").Select
Selection.Copy
Sheets("Sheet2").Select
Range("EZ3").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("H4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("EZ5").Select
ActiveSheet.Paste
End Sub

Sub Macro2 Insert Text in Sheet 2 and Sheet 3
Sheets("Sheet2").Select Range("EZ13").Select
ActiveCell.FormulaR1C1 = "TXT MONDAY"
Sheets("Sheet3").Select
Range("EZ13").Select
ActiveCell.FormulaR1C1 = "TXT MONDAY"
Range("EZ14").Select
End Sub

Sub Macro3

No ideas how to record: ** MACRO MUST CHANGE COLOR FOR SHEET 2 EZ13 DEPENDING IF THE NUMBER IN SHEET 2 EZ3 IS ABOVE ZERO TO RGB ( 0, 135, 60) OR BELOW ZERO TO RGB (235, 15, 41), THIS CASE RED.

End Sub

Sub Macro4 Insert Hyperlink from Sheet 2 to Sheet 3 and from Sheet 3 to Sheet 2
Sheets("Sheet2").Select
Range("EZ13").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Sheet3!EZ13", TextToDisplay:="TXT MONDAY"
Sheets("Sheet3").Select
Range("EZ13").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"Sheet2!EZ13", TextToDisplay:="TXT MONDAY"

'Could Macro 4 superseded Macro 2 ? Why ? Because in Macro 4 i can insert text wich would make Macro 2 useless ???
End Sub

[BrianD]

Hello

I upload my file as test. as u see i marked for 1 week but i need the code for one day, and then i will figure it out for the other remaining days29400

[SamT]

In Re converting Recorded MAcros to VBA Routines

Start with:

Sub Macro1 Copy from Sheet 1 to Sheet 2
Sheets("Sheet1").Range("G3:G4").Copy
Sheets("Sheet2").Range("EZ3").Paste
Sheets("Sheet1"). Range("H4").Copy
Sheets("Sheet2").Range("EZ5").Paste
End Sub

Finish with

Sub Copy_1to2()
Dim Sh1 As Object, Sh2 as Object
'Or Dim Sh1 as Worksheet, Sh2 As Worksheet
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")

Sh2.Range("EZ3:EZ4") = Sh1.Range("G3:G4")
Sh2.Range("EZ5") = sh1.Range("H4")
End Sub