I am trying really hard to teach myself VBA through trial and error. I have the following that is doing exactly what I want it to do. And I'm sure there is a much better and more elegant way to do it, but it does work.
What I cannot figure out is how to change move to the next row of both the source and target worksheets for the cells shown below in bold red font. This is the first of probably many things I ultimately want this sub to do so any help will be another learning tool for me.
Sub CopyValuesBetweenWorksheets()
Dim sourceWS As Worksheet
Dim targetWS As Worksheet
' Set the source and target worksheets
Set sourceWS = ThisWorkbook.Worksheets("MRC_4699A") ' eventually replace "MRC_4699A" with the next sheet with a name LIKE {"***_*****}
Set targetWS = ThisWorkbook.Worksheets("Daily Summary") '
' Copy values from specific cells in the source sheet
targetWS.Range("B21").Value = sourceWS.Range("f4").Value
targetWS.Range("c21").Value = sourceWS.Range("m4").Value
targetWS.Range("d21").Value = sourceWS.Range("q1").Value
targetWS.Range("f21").Value = sourceWS.Range("e18").Value
targetWS.Range("g21").Value = sourceWS.Range("f18").Value
targetWS.Range("h21").Value = sourceWS.Range("g18").Value
targetWS.Range("i21").Value = sourceWS.Range("h18").Value
targetWS.Range("E21").Value = ""
If sourceWS.Range("AB3").Value Or sourceWS.Range("AB4").Value = True Then
targetWS.Range("E21").Value = "Pickup/SUV"
End If
Dim nonBlankCount1 As Long
nonBlankCount1 = Application.WorksheetFunction.Count(sourceWS.Range("K18:M18"))
targetWS.Range("k21") = nonBlankCount1
Dim nonBlankCount2 As Long
nonBlankCount2 = Application.WorksheetFunction.Count(sourceWS.Range("N18"))
targetWS.Range("L21") = nonBlankCount2
If nonBlankCount2 = 1 Then
targetWS.Range("L21") = "yes"
End If
End Sub
What I cannot figure out is how to change move to the next row of both the source and target worksheets for the cells shown below in bold red font. This is the first of probably many things I ultimately want this sub to do so any help will be another learning tool for me.
Sub CopyValuesBetweenWorksheets()
Dim sourceWS As Worksheet
Dim targetWS As Worksheet
' Set the source and target worksheets
Set sourceWS = ThisWorkbook.Worksheets("MRC_4699A") ' eventually replace "MRC_4699A" with the next sheet with a name LIKE {"***_*****}
Set targetWS = ThisWorkbook.Worksheets("Daily Summary") '
' Copy values from specific cells in the source sheet
targetWS.Range("B21").Value = sourceWS.Range("f4").Value
targetWS.Range("c21").Value = sourceWS.Range("m4").Value
targetWS.Range("d21").Value = sourceWS.Range("q1").Value
targetWS.Range("f21").Value = sourceWS.Range("e18").Value
targetWS.Range("g21").Value = sourceWS.Range("f18").Value
targetWS.Range("h21").Value = sourceWS.Range("g18").Value
targetWS.Range("i21").Value = sourceWS.Range("h18").Value
targetWS.Range("E21").Value = ""
If sourceWS.Range("AB3").Value Or sourceWS.Range("AB4").Value = True Then
targetWS.Range("E21").Value = "Pickup/SUV"
End If
Dim nonBlankCount1 As Long
nonBlankCount1 = Application.WorksheetFunction.Count(sourceWS.Range("K18:M18"))
targetWS.Range("k21") = nonBlankCount1
Dim nonBlankCount2 As Long
nonBlankCount2 = Application.WorksheetFunction.Count(sourceWS.Range("N18"))
targetWS.Range("L21") = nonBlankCount2
If nonBlankCount2 = 1 Then
targetWS.Range("L21") = "yes"
End If
End Sub