I'm trying to run a macro of my book 2 ("Corrections_Data.xlsm") from my book 1 ("Machine_Data.xls"), in my book 1 I have two variables called "k_miter" and "k_machine" that count the last line filled in a certain column in a specific spreadsheet. For the value of the variable to pass from one book to another I created a parameter in book 2, but for some reason the value of the variable is not passed between the 2 books, can someone help me?
Book1:
Book2:
Book1:
Code:
Private Sub FillCells()
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Activate
k_machine = Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(Rows.Count, "B").End(xlUp).Offset(1).Row
Cells(k_machine, "B").Value = MachineName
Cells(k_machine, "C").Value = N_OT
Cells(k_machine, "D").Value = Tec
Cells(k_machine, "E").Value = Date_Todayt
Cells(k_machine, "F").Value = hour
Cells(k_machine, "G").Value = A_Grid_Orig
Cells(k_machine, "H").Value = C_Grid_Orig
Cells(k_machine, "I").Value = A2
Cells(k_machine, "J").Value = A4
Cells(k_machine, "K").Value = Xrel_90
'AC After
Cells(k_machine, "M").Value = A_Grid_Orig_D
Cells(k_machine, "N").Value = C_Grid_Orig_D
Cells(k_machine, "O").Value = A2_D
Cells(k_machine, "P").Value = A4_D
Cells(k_machine, "Q").Value = Xrel_90_D
'Sphere Before
Cells(k_machine, "S").Value = P666
Cells(k_machine, "T").Value = P709
Cells(k_machine, "U").Value = P710
Cells(k_machine, "V").Value = P713
Cells(k_machine, "W").Value = A0
Cells(k_machine, "X").Value = A_90
Cells(k_machine, "Y").Value = A90
Cells(k_machine, "Z").Value = Delta_A
'Sphere After
Cells(k_machine, "AB").Value = P666r
Cells(k_machine, "AC").Value = P709r
Cells(k_machine, "AD").Value = P710r
Cells(k_machine, "AE").Value = P713r
Cells(k_machine, "AF").Value = A0r
Cells(k_machine, "AG").Value = A_90r
Cells(k_machine, "AH").Value = A90r
Cells(k_machine, "AI").Value = Delta
'Compensation Before
Cells(k_machine, "AK").Value = C0_A
Cells(k_machine, "AL").Value = C315_A
Cells(k_machine, "AM").Value = C270_A
Cells(k_machine, "AN").Value = C225_A
Cells(k_machine, "AO").Value = C180_A
Cells(k_machine, "AP").Value = C135_A
Cells(k_machine, "AQ").Value = C90_A
Cells(k_machine, "AR").Value = C45_A
Cells(k_machine, "AS").Value = InitialSpread
'Compensation After
Cells(k_machine, "AU").Value = C0
Cells(k_machine, "AV").Value = C315
Cells(k_machine, "AW").Value = C270
Cells(k_machine, "AX").Value = C225
Cells(k_machine, "AY").Value = C180
Cells(k_machine, "AZ").Value = C135
Cells(k_machine, "BA").Value = C90
Cells(k_machine, "BB").Value = C45
'Spreads
Cells(k_machine, "BE").Value = InitialSpread
Cells(k_machine, "BF").Value = InitialSpread
Cells(k_machine, "BG").Value = Runnout
Application.Run "Corrections_Data.xlsm! Text_In_Cells_Offset", k_miter
End Sub
Book2:
VBA Code:
Public Sub Text_In_Cells_Offset(k_machine As Long)
'Color Cells 1
If Cells(k_machine, "I").Value < 0 And Cells(k_machine, "J").Value < 0 Then
If Cells(k_machine, "I") + Cells(k_machine, "J") * 1 > 30 Or Cells(k_machine, "I") - Cells(k_machine, "J") * 1 > 13 Or Cells(k_machine, "I") + Cells(k_machine, "J") * 1 < -30 Or Cells(k_machine, "I") - Cells(k_machine, "J") * 1 < -13 Then
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 3
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Correção Necessária"
ElseIf Cells(k_machine, "I") + Cells(k_machine, "J") * 1 <= 30 & Cells(k_machine, "I") - Cells(k_machine, "J") * 1 <= 13 Or Cells(k_machine, "I") + Cells(k_machine, "J") * 1 >= -30 & Cells(k_machine, "I") - Cells(k_machine, "J") * 1 >= -13 Then
ActiveCell.Interior.ColorIndex = 4
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Font.Color = vbBlack
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Não Precisa de Correção"
End If
ElseIf Cells(k_machine, "I").Value > 0 And Cells(k_machine, "J").Value < 0 Then
If Cells(k_machine, "I") + Cells(k_machine, "J") * 1 > 30 Or Cells(k_machine, "I") - Cells(k_machine, "J") * 1 > 13 Or Cells(k_machine, "I") + Cells(k_machine, "J") * 1 < -30 Or Cells(k_machine, "I") - Cells(k_machine, "J") * 1 < -13 Then
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 3
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Correção Necessária"
ElseIf Cells(k_machine, "I") + Cells(k_machine, "J") * 1 <= 30 & Cells(k_machine, "I") - Cells(k_machine, "J") * 1 <= 13 Or Cells(k_machine, "I") + Cells(k_machine, "J") * 1 >= -30 & Cells(k_machine, "I") - Cells(k_machine, "J") * 1 >= -13 Then
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 4
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Font.Color = vbBlack
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Não Precisa de Correção"
End If
ElseIf Cells(k_machine, "I").Value > 0 And Cells(k_machine, "J").Value > 0 Then
If Cells(k_machine, "I") + Cells(k_machine, "J") * 1 > 30 Or Cells(k_machine, "I") - Cells(k_machine, "J") * 1 > 13 Or Cells(k_machine, "I") + Cells(k_machine, "J") * 1 < -30 Or Cells(k_machine, "I") - Cells(k_machine, "J") * 1 < -13 Then
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 3
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Correção Necessária"
ElseIf Cells(k_machine, "I") + Cells(k_machine, "J") * 1 <= 30 & Cells(k_machine, "I") - Cells(k_machine, "J") * 1 <= 13 Or Cells(k_machine, "I") + Cells(k_machine, "J") * 1 >= -30 & Cells(k_machine, "I") - Cells(k_machine, "J") * 1 >= -13 Then
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 4
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Font.Color = vbBlack
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Não Precisa de Correção"
End If
ElseIf Cells(k_machine, "I").Value < 0 And Cells(k_machine, "J").Value > 0 Then
If Cells(k_machine, "I") + Cells(k_machine, "J") * 1 > 30 Or Cells(k_machine, "I") - Cells(k_machine, "J") * 1 > 13 Or Cells(k_machine, "I") + Cells(k_machine, "J") * 1 < -30 Or Cells(k_machine, "I") - Cells(k_machine, "J") * 1 < -13 Then
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 3
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Correção Necessária"
ElseIf Cells(k_machine, "I") + Cells(k_machine, "J") * 1 <= 30 & Cells(k_machine, "I") - Cells(k_machine, "J") * 1 <= 13 Or Cells(k_machine, "I") + Cells(k_miter, "J") * 1 >= -30 & Cells(k_machine, "I") - Cells(k_machine, "J") * 1 >= -13 Then
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 4
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Font.Color = vbBlack
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Não Precisa de Correção"
End If
End If
'ColorCells2
If Cells(k_machine, "K").Value < -13 * 1 Or Cells(k_machine, "K").Value > 13 Then
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 3
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Correção Necessária"
ElseIf Cells(k_machine, "K").Value >= -13 * 1 Or Cells(k_machine, "K").Value <= 13 Then
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Interior.ColorIndex = 4
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Font.Color = vbBlack
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "L").Value = "Não Precisa de Correção"
End If
'Color Cells 3
If Cells(k_machine, "O").Value < 0 And Cells(k_machine, "P").Value < 0 Then
If Cells(k_machine, "O") + Cells(k_machine, "P") * 1 > 30 Or Cells(k_machine, "O") - Cells(k_machine, "P") * 1 > 13 Or Cells(k_machine, "O") + Cells(k_machine, "P") * 1 < -30 Or Cells(k_machine, "O") - Cells(k_machine, "P") * 1 < -13 Then
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Interior.ColorIndex = 3
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Value = "Correção Necessária"
ElseIf Cells(k_machine, "O") + Cells(k_machine, "P") * 1 <= 30 & Cells(k_machine, "O") - Cells(k_machine, "P") * 1 <= 13 Or Cells(k_machine, "O") + Cells(k_machine, "P") * 1 >= -30 & Cells(k_machine, "O") - Cells(k_machine, "P") * 1 >= -13 Then
ActiveCell.Interior.ColorIndex = 4
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Font.Color = vbBlack
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Value = "Não Precisa de Correção"
End If
ElseIf Cells(k_machine, "O").Value > 0 And Cells(k_machine, "P").Value < 0 Then
If Cells(k_machine, "O") + Cells(k_machine, "P") * 1 > 30 Or Cells(k_machine, "O") - Cells(k_machine, "P") * 1 > 13 Or Cells(k_machine, "O") + Cells(k_machine, "P") * 1 < -30 Or Cells(k_machine, "O") - Cells(k_machine, "P") * 1 < -13 Then
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Interior.ColorIndex = 3
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Value = "Correção Necessária"
ElseIf Cells(k_machine, "O") + Cells(k_machine, "P") * 1 <= 30 & Cells(k_machine, "O") - Cells(k_machine, "P") * 1 <= 13 Or Cells(k_machine, "O") + Cells(k_machine, "P") * 1 >= -30 & Cells(k_machine, "O") - Cells(k_machine, "P") * 1 >= -13 Then
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Interior.ColorIndex = 4
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Font.Color = vbBlack
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Value = "Não Precisa de Correção"
End If
ElseIf Cells(k_machine, "O").Value > 0 And Cells(k_machine, "P").Value > 0 Then
If Cells(k_machine, "O") + Cells(k_machine, "P") * 1 > 30 Or Cells(k_machine, "O") - Cells(k_machine, "P") * 1 > 13 Or Cells(k_machine, "O") + Cells(k_machine, "P") * 1 < -30 Or Cells(k_machine, "O") - Cells(k_machine, "P") * 1 < -13 Then
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Interior.ColorIndex = 3
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Value = "Correção Necessária"
ElseIf Cells(k_machine, "O") + Cells(k_machine, "P") * 1 <= 30 & Cells(k_machine, "O") - Cells(k_machine, "P") * 1 <= 13 Or Cells(k_machine, "O") + Cells(k_machine, "P") * 1 >= -30 & Cells(k_machine, "O") - Cells(k_machine, "P") * 1 >= -13 Then
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Interior.ColorIndex = 4
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Font.Color = vbBlack
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Value = "Não Precisa de Correção"
End If
ElseIf Cells(k_machine, "I").Value < 0 And Cells(k_machine, "J").Value > 0 Then
If Cells(k_machine, "O") + Cells(k_machine, "P") * 1 > 30 Or Cells(k_machine, "O") - Cells(k_machine, "P") * 1 > 13 Or Cells(k_machine, "O") + Cells(k_machine, "P") * 1 < -30 Or Cells(k_machine, "O") - Cells(k_machine, "P") * 1 < -13 Then
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Interior.ColorIndex = 3
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Value = "Correção Necessária"
ElseIf Cells(k_machine, "O") + Cells(k_machine, "P") * 1 <= 30 & Cells(k_machine, "O") - Cells(k_machine, "P") * 1 <= 13 Or Cells(k_machine, "O") + Cells(k_miter, "P") * 1 >= -30 & Cells(k_machine, "O") - Cells(k_machine, "P") * 1 >= -13 Then
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Interior.ColorIndex = 4
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Font.Color = vbBlack
Workbooks("Corrections_Data.xlsm").Sheets("AC_Offset_Registers").Cells(k_machine, "R").Value = "Não Precisa de Correção"
End If
End If