Run my master workbook and call another non-macro enabled workbook and do the edits to it prior to saving

[Nlhicks]

I am getting an error on the line RngRang01 = Range("A" & Rows.Count).End(xlUp).Row and then when I move it into a with block I get an error on the line Sheet1.Range("B2:B" & RngRange01).Font.Color = vbRed. I had this code running on the workbook that it lives in and it worked marvelously now my supervisor asked that I make it a stand alone macro workbook that I can use to change the saved/shared file. I am not sure what I need to do at this point to make it work. I was able to get some of the others to work but this one is causing issues.



Sub LineUpdate1()
'Last update 11/16/2022 by NLH
'Line Update Task List
'Compares what the user enters as Changes to what is in the existing spreadsheet.
'If there is a difference: The font color changes to red and the number is updated to match the user input. Otherwise if there is no change it keeps the original formatting and information.
'It then does the math to compute the difference between what was and what is now and defines it as uprate/downrate in a table to the right.
'Then it concatenates all of the values together to paste into an email and that is in a table down below.
'This module and the next 4 (Module 3,4,5,6,7) are all pretty much the same but each one is for a new change if more than one are made.
Application.ScreenUpdating = False

Dim RngRange01 As Range
Dim Wb As Workbook
Dim LineUpdate As Worksheet, Sheet2 As Worksheet
Dim Ws As Range
Dim Rowz As Integer


Windows("WAPA-UGPR Facility Rating and SOL Record (Master).xlsm").Activate
Sheets("Line Update").Activate
Set LineUpdate = Sheets("Line Update")

'Workbooks.Open Filename:="WAPA-UGPR Facility Rating and SOL Record (Test Workbook).xlsm"
Windows("WAPA-UGPR Facility Rating and SOL Record (Test Workbook).xlsm").Activate
Sheets("Facility Ratings & SOLs (Lines)").Activate

Set Sheet1 = Sheets("Facility Ratings & SOLs (Lines)")
Set Ws = Sheet1.UsedRange
With LineUpdate
'RngRang01 = Range("A" & Rows.Count).End(xlUp).Row
With Sheet1.Range("A1")
LineUpdate.Range("J13").Value = Sheet1.Range("A2:A685").SpecialCells(xlCellTypeVisible)

If LineUpdate.Range("C11") <> LineUpdate.Range("F11") And LineUpdate.Range("F11") <> "" Then
Sheet1.Range("B2:B" & RngRange01).Font.Color = vbRed
Sheet1.Range("B2:B" & RngRange01).Value = LineUpdate.Range("F11").Value
Else
If LineUpdate.Range("F11") = "" Then
Sheet1.Range("B2:B" & RngRange01).Value = Sheet1.Range("B2:B" & RngRange01).Value
End If
End If

If LineUpdate.Range("C12") <> LineUpdate.Range("F12") And LineUpdate.Range("F12") <> "" Then
Sheet1.Range("C2:C" & RngRange01).Font.Color = vbRed
Sheet1.Range("C2:C" & RngRange01).Value = LineUpdate.Range("F12").Value
Else

If LineUpdate.Range("F12") = "" Then
Sheet1.Range("C2:C" & RngRange01).Value = Sheet1.Range("C2:C" & RngRange01).Value

End If
End If


If LineUpdate.Range("C13") <> LineUpdate.Range("F13") And LineUpdate.Range("F13") <> "" Then
Sheet1.Range("D2:D" & RngRange01).Font.Color = vbRed
Sheet1.Range("D2:D" & RngRange01).Value = LineUpdate.Range("F13").Value
Else
If Worksheets("Line Update").Range("F13") = "" Then
Sheet1.Range("D2:D" & RngRange01).Value = Sheet1.Range("D2:D" & RngRange01).Value
End If
End If


If LineUpdate.Range("C14") <> LineUpdate.Range("F14") And LineUpdate.Range("F14") <> "" Then
Sheet1.Range("E2:E" & RngRange01).Font.Color = vbRed
Sheet1.Range("E2:E" & RngRange01).Value = Worksheets("Line Update").Range("F14").Value
Else
If LineUpdate.Range("F14") = "" Then
Sheet1.Range("E2:E" & RngRange01).Value = Sheet1.Range("E2:E").Value

End If
End If

If LineUpdate.Range("C15") <> LineUpdate.Range("F15") And LineUpdate.Range("F15") <> "" Then
Sheet1.Range("F2:F").Font.Color = vbRed
Sheet1.Range("F2:F" & RngRang01).Value = LineUpdate.Range("F15").Value
Else
If LineUpdate.Range("F15") = "" Then
Sheet1.Range("F2:F" & RngRang01).Value = Sheet1.Range("F2:F" & RngRang01).Value
End If
End If

If LineUpdate.Range("C16") <> LineUpdate.Range("F16") And LineUpdate.Range("F16") <> "" Then
Sheet1.Range("G2:G" & RngRang01).Font.Color = vbRed
Sheet1.Range("G2:G" & RngRang01).Value = WLineUpdate.Range("F16").Value
Else
If LineUpdate.Range("F16") = "" Then
Sheet1.Range("G2:G" & RngRang01).Value = Sheet1.Range("G2:G" & RngRang01).Value
End If
End If

If LineUpdate.Range("C17") <> LineUpdate.Range("F17") And LineUpdate.Range("F17") <> "" Then
Sheet1.Range("H2:H" & RngRang01).Font.Color = vbRed
Sheet1.Range("H2:H" & RngRang01).Value = LineUpdate.Range("F17").Value
Else
If LineUpdate.Range("F17") = "" Then
Sheet1.Range("H2:H" & RngRang01).Value = Sheet1.Range("H2:H" & RngRang01).Value
End If
End If

If LineUpdate.Range("C18") <> LineUpdate.Range("F18") And LineUpdate.Range("F18") <> "" Then
Sheet1.Range("I2:I" & RngRang01).Font.Color = vbRed
Sheet1.Range("I2:I" & RngRang01).Value = LineUpdate.Range("F18").Value
Else
If LineUpdate.Range("F18") = "" Then
Sheet1.Range("I2:I" & RngRang01).Value = Sheet1.Range("I2:I" & RngRang01).Value
End If
End If

'Worksheets("Line Update").Activate
End With
End With

Call LineColorCells

Call DoLineMath1
Application.ScreenUpdating = True

End Sub

 

[Nlhicks]

Good morning Holger, I ran the code you produced above and this is my result. What should happen is, the range from workbook WAPA-UGPR Facility Rating and SOL (Master) Worksheet Line Update F11:F18 should be copied into each respective column of worksheet Facility Rating & SOL (Lines) in the workbook WAPA-UGPR Facility Rating and SOL Record (Data File)_vxxx columns 2:8 or B:I

 

[Nlhicks]

I cannot even test the other code work until I get the line update working properly, it is one step at a time through the proess.

 

[Nlhicks]

GetWorkbook_Worksheet cstrPath, cstrWbData, wbkData, cstrShData, wksFrom

For this line of code I see cstrPath, WbData and ShData all defined but I do not see wbkData and wksFrom defined before it is called here. The code uses those several times but it does not seem to be defined. Could that be the problem?

 

[HaHoBe]

Hi Nlhicks,

could you please specify which code and what procedure you referring to? I tend to believe it's LineUpdate1 but I have a couple of these and would like to limit the search to only the code you are having trouble with.

GetWorkbook_Worksheet cstrPath, cstrWbData, wbkData, cstrShData, wksFrom

I guess this comes from MrE_1223414_1615014_LineUpdate_New. A look at the dims:

  Dim wbkData           As Workbook     'data workbook, to be filled from Sub
  Dim wksFrom           As Worksheet    'data workbook, to be filled from Sub
  Dim wbkTarget         As Workbook
  Dim wksWorkOn         As Worksheet

  Const cstrPath        As String = "C:\Users\nhicks\Documents\Ratings\Saved Versions\"
  Const cstrWbData      As String = "WAPA-UGPR Facility Rating and SOL Record (Master).xlsm"
  Const cstrShData      As String = "Line Update"

  Const cstrStFileName  As String = "WAPA-UGPR Facility Rating and SOL Record (Data File)_v"
  Const cstrShFacility  As String = "Facility Ratings & SOLs (Lines)"
 
  GetWorkbook_Worksheet cstrPath, cstrWbData, wbkData, cstrShData, wksFrom

The variables wbkData as well as wksFrom are used as empty parameters and will be filled after GetWorkbook_Worksheet has been finished. From the codeline after the one you mentioned the variables will be checked and if everything is correct hold the proper object to the wanted workbook and worksheet.

Holger

 

[Nlhicks]

Okay I see, can we go back to line by line like I had it with your language? Such as compare C11 to F11 in line update and if empty keep C11 else if different change the workbook (Data File) range B2:B=F11 and vbRed. Then move to C12 to F12 and change C2:C but also put in the SpecialCells(xlCellTypeVisible) associated with lngLastRow or in my code RngRange01?

 

[Nlhicks]

I know that part of my code worked perfectly but it did not work in two separate workbooks however you have made it work in two separate workbooks so I know that if you can do line by line like I had it except with the two workbooks then it should work. Any thoughts?

 

[HaHoBe]

Which post, what procedure?

 

[Nlhicks]

Good morning Holger, I ran the code you produced above and this is my result. What should happen is, the range from workbook WAPA-UGPR Facility Rating and SOL (Master) Worksheet Line Update F11:F18 should be copied into each respective column of worksheet Facility Rating & SOL (Lines) in the workbook WAPA-UGPR Facility Rating and SOL Record (Data File)_vxxx columns 2:8 or B:I

Attachments​

• 
  Result.PNG
  67.9 KB · Views: 1
• 

Line Update, the latest version you posted provided me with this result. Not only the header and the line below got updated but all with only the one number and not the full range.

 

[HaHoBe]

Hi Nlhicks,

the latest version I posted is from #49 and was a slightly modified version of the opening post where I did not implement any new parts.

What I look at is something that I would still solve in taking every single row of the data range and fitting that in into the filtered area - even with my version of Excel 2019 copied data is filled in chronologicly starting with the first visible row and putting in the other rows just below (if you will: ignoring that any user wants to fill only the visible cells).

It will take a little time to adapt as I never realized that a part of the code you posted wasn't working the way you wanted it to.

Holger

 

[HaHoBe]

Hi Nlhicks,

looking at the pictures I found that the target should not be a range but a single cell instead: a cell from the first visible row on "Facility Ratings & SOLs (Lines)" after the heading.

I tried to fully qualify all ranges giving them the object for the sheet and changed some lines in order to make this code easier to maintain by using a With for each target cell. Only if the code does what it should do we can take the step to use a loop in order to shorten the code and work on setting the objects for teh workbooks and worksheets (but that is future...).

Sub LineUpdate1_mod221205()
'Last update 11/16/2022 by NLH
'Line Update Task List
'Compares what the user enters as Changes to what is in the existing spreadsheet.
'If there is a difference: The font color changes to red and the number is updated to match the user input. Otherwise if there is no change it keeps the original formatting and information.
'It then does the math to compute the difference between what was and what is now and defines it as uprate/downrate in a table to the right.
'Then it concatenates all of the values together to paste into an email and that is in a table down below.
'This module and the next 4 (Module 3,4,5,6,7) are all pretty much the same but each one is for a new change if more than one are made.

'/// starting code for this thread
'/// modified by HaHoBe, 20221205

Dim lngTarget         As Long
Dim wsLineUpdate      As Worksheet
Dim wsSOLines         As Worksheet

Application.ScreenUpdating = False
Windows("WAPA-UGPR Facility Rating and SOL Record (Master).xlsm").Activate
Sheets("Line Update").Activate
Set wsLineUpdate = Sheets("Line Update")

'Workbooks.Open Filename:="WAPA-UGPR Facility Rating and SOL Record (Test Workbook).xlsm"
Windows("WAPA-UGPR Facility Rating and SOL Record (Test Workbook).xlsm").Activate
Sheets("Facility Ratings & SOLs (Lines)").Activate
Set wsSOLines = Sheets("Facility Ratings & SOLs (Lines)")

With wsSOLines
  lngTarget = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1).Row
  wsLineUpdate.Range("J13").Value = .Range("A2:A685").SpecialCells(xlCellTypeVisible).Cells.Count

  With .Range("B" & lngTarget)
    If wsLineUpdate.Range("C11") <> wsLineUpdate.Range("F11") And wsLineUpdate.Range("F11") <> "" Then
      .Font.Color = vbRed
      .Value = wsLineUpdate.Range("F11").Value
    Else
      If wsLineUpdate.Range("F11") = "" Then
        .Value = .Value
      End If
    End If
  End With
  
  With .Range("C" & lngTarget)
    If wsLineUpdate.Range("C12") <> wsLineUpdate.Range("F12") And wsLineUpdate.Range("F12") <> "" Then
      .Font.Color = vbRed
      .Value = wsLineUpdate.Range("F12").Value
    Else
      If wsLineUpdate.Range("F12") = "" Then
        .Value = .Value
      End If
    End If
  End With

  With .Range("D" & lngTarget)
    If wsLineUpdate.Range("C13") <> wsLineUpdate.Range("F13") And wsLineUpdate.Range("F13") <> "" Then
      .Font.Color = vbRed
      .Value = wsLineUpdate.Range("F13").Value
    Else
      If wsLineUpdate.Range("F13") = "" Then
        .Value = .Value
      End If
    End If
  End With

  With .Range("E" & lngTarget)
    If wsLineUpdate.Range("C14") <> wsLineUpdate.Range("F14") And wsLineUpdate.Range("F14") <> "" Then
      .Font.Color = vbRed
      .Value = wsLineUpdate.Range("F14").Value
    Else
      If wsLineUpdate.Range("F14") = "" Then
        .Value = .Value
      End If
    End If
  End With

  With .Range("F" & lngTarget)
    If wsLineUpdate.Range("C15") <> wsLineUpdate.Range("F15") And wsLineUpdate.Range("F15") <> "" Then
      .Font.Color = vbRed
      .Value = wsLineUpdate.Range("F15").Value
    Else
      If wsLineUpdate.Range("F15") = "" Then
        .Value = .Value
      End If
    End If
  End With
  
  With .Range("G" & lngTarget)
    If wsLineUpdate.Range("C16") <> wsLineUpdate.Range("F16") And wsLineUpdate.Range("F16") <> "" Then
      .Font.Color = vbRed
      .Value = wsLineUpdate.Range("F16").Value
    Else
      If wsLineUpdate.Range("F16") = "" Then
        .Value = .Value
      End If
    End If
  End With

  With .Range("H" & lngTarget)
    If wsLineUpdate.Range("C17") <> wsLineUpdate.Range("F17") And wsLineUpdate.Range("F17") <> "" Then
      .Font.Color = vbRed
      .Value = wsLineUpdate.Range("F17").Value
    Else
      If wsLineUpdate.Range("F17") = "" Then
        .Value = .Value
      End If
    End If
  End With

  With .Range("I" & lngTarget)
    If wsLineUpdate.Range("C18") <> wsLineUpdate.Range("F18") And wsLineUpdate.Range("F18") <> "" Then
      .Font.Color = vbRed
      .Value = wsLineUpdate.Range("F18").Value
    Else
      If wsLineUpdate.Range("F18") = "" Then
        .Value = .Value
      End If
    End If
  End With

  'Worksheets("Line Update").Activate
End With

'/// commented next codeline in order to check only this code
'Call LineColorCells

'/// commented next codeline in order to check only this code
'Call DoLineMath1
Set wsSOLines = Nothing
Set wsLineUpdate = Nothing
Application.ScreenUpdating = True

End Sub

Ciao,
Holger