VBA to copy and convert data to a different sheet

[alisoncleverly]

Hi everyone,
I'm trying to both copy and convert data from 1 sheet to another. Currently my team has to use 3 different sheets just for the purpose of getting clean data.

To be more specific,
1st: Raw data is first pasted into a sheet called "Paste Target Data Here".
2nd: From there, the "Converted" sheet which has Excel formulas in each column would take value from PTDH sheet and convert the data into some specific values (depending on the function used in each column)
3rd: Now, my team would have to copy and paste as value everything on "Converted" sheet to "Master Worksheet" which shares the exact column order with "Converted".

I believe this long procedure can be reduced to just 2 tabs/sheets and bypass the "Converted" part: one to paste Raw data, another to convert and get the final, clean data.
Here is what I currently have in my Module:

Sub UpdateMaster()
Dim r As Range
Dim wsMaster As Worksheet, wsSAP as Worksheet
Dim LastR As Integer

If MsgBox("Update data from 'SAP' to 'Master Worksheet'?", _
vbYesNo + vbQuestion +vbDefaultButton2, "Update Master") = vbNo Then
   Exit Sub
End If

Set wsMaster = Thisworkbook.Worksheets("Master Worksheet")
Set wsSAP = ThisWorkbook.Worksheets("SAP")

LastR = wsSAP.Columns("J:J, X:X, AA:AD, AI:AM, AP:AP, AY:AY").Find(What:="*",_
SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

'Turn Off Events
   Application.EnableEvents = False
'Get rid of old data
   wsMaster.Cells.ClearContents
'Copy Columns from SAP to Master
   wsSAP.Range("AY2:AY" & LastR).Copy Destination:=wsMaster.Range("A2")
   wsSAP.Range("C2:C" & LastR).Copy Destination:=wsMaster.Range("B2")
   'Code for Column C here - use If statement
   wsSAP.Range("AB2:AB" & LastR).Copy Destination:=wsMaster.Range("D2")
   wsSAP.Range("AA2:AA" & LastR).Copy Destination:=wsMaster.Range("E2")
   wsSAP.Range("AC2:AC" & LastR).Copy Destination:=wsMaster.Range("F2")
   wsSAP.Range("AD2:AD" & LastR).Copy Destination:=wsMaster.Range("G2")
   wsSAP.Range("AI2:AI" & LastR).Copy Destination:=wsMaster.Range("H2")
   wsSAP.Range("AJ2:AJ" & LastR).Copy Destination:=wsMaster.Range("I2")
   wsSAP.Range("AK2:AK" & LastR).Copy Destination:=wsMaster.Range("J2")
   wsSAP.Range("AL2:AL" & LastR).Copy Destination:=wsMaster.Range("K2")
   wsSAP.Range("AP2:AP" & LastR).Copy Destination:=wsMaster.Range("L2")
   'Code for Column M here - use If statement
   'Code for Column AU here - use If statement
   'Code for Column AV here - use If statement
   'Code for Column AW here - use If statement
   'Code for Column AX here - use If statement

'Add formulas
Set r = wsMaster.Cells(1, 1).CurrentRegion.Columns(17)
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
r.Formula = "=IF(AND(N2=""Podding"", O2=""Rollup""),""Podding"",IF(O2=N2,""Sales/Production"",IF(P2=O2,""Production"",IF(P2=N2,""Sales"",""""))))"

Set r = wsMaster.Cells(1, 1).CurrentRegion.Columns(21)
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
r.Formula = "=IF(AND(R2=""Podding"", S2=""Rollup""),""Podding"",IF(AND(R2=""Shipped"", S2=""Rollup""),""Sales/Production"",IF(R2=S2,""Sales/Production"",IF(S2=T2,""Production"",IF(R2=T2,""Sales"","""")))))"

Set r = wsMaster.Cells(1, 1).CurrentRegion.Columns(25)
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
r.Formula = "=IF(AND(V2=""Podding"", W2=""Rollup""),""Podding"",IF(AND(V2=""Shipped"", W2=""Rollup""),""Sales/Production"",IF(V2=W2,""Sales/Production"",IF(W2=X2,""Production"",IF(V2=X2,""Sales"","""")))))"

Set r = wsMaster.Cells(1, 1).CurrentRegion.Columns(29)
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
r.Formula = "=IF(AND(Z2=""Podding"", AA2=""Rollup""),""Podding"",IF(AND(Z2=""Shipped"", AA2=""Rollup""),""Sales/Production"",IF(Z2=AA2,""Sales/Production"",IF(AA2=AB2,""Production"",IF(Z2=AB2,""Sales"","""")))))"

Set r = wsMaster.Cells(1, 1).CurrentRegion.Columns(33)
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
r.Formula = "=IF(AND(AD2=""Podding"", AE2=""Rollup""),""Podding"",IF(AND(AD2=""Shipped"", AE2=""Rollup""),""Sales/Production"",IF(AD2=AE2,""Sales/Production"",IF(AE2=AF2,""Production"",IF(AD2=AF2,""Sales"","""")))))"

Set r = wsMaster.Cells(1, 1).CurrentRegion.Columns(37)
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
r.Formula = "=IF(AND(AH2=""Podding"", AI2=""Rollup""),""Podding"",IF(AND(AH2=""Shipped"", AI2=""Rollup""),""Sales/Production"",IF(AH2=AI2,""Sales/Production"",IF(AI2=AJ2,""Production"",IF(AH2=AJ2,""Sales"","""")))))"

Set r = wsMaster.Cells(1, 1).CurrentRegion.Columns(41)
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
r.Formula = "=IF(AND(AL2=""Podding"", AM2=""Rollup""),""Podding"",IF(AND(AL2=""Shipped"", AM2=""Rollup""),""Sales/Production"",IF(AL2=AM2,""Sales/Production"",IF(AM2=AN2,""Production"",IF(AL2=AN2,""Sales"","""")))))"

Set r = wsMaster.Cells(1, 1).CurrentRegion.Columns(45)
Set r = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
r.Formula = "=IF(AND(AP2=""Podding"", AQ2=""Rollup""),""Podding"",IF(AND(AP2=""Shipped"", AQ2=""Rollup""),""Sales/Production"",IF(AP2=AQ2,""Sales/Production"",IF(AQ2=AR2,""Production"",IF(AP2=AR2,""Sales"","""")))))"

'Turn On Events
   Application.EnableEvents = True

End Sub

1) My first problem is, for this part of codes

wsSAP.Range("AB2:AB" & LastR).Copy Destination:=wsMaster.Range("D2")

, I have many similar lines in which I wanted to copy 1 column in "SAP" to another column in "Master Worksheet". However, I realised it wasn't the best way since I know for sure column AB in "SAP" won't have empty/blank cells from first row to last row. However, that isn't the case for other columns. Some columns might have blank/empty cells somewhere within their data ranges and my LastR might take it as the last non-empty cell and stop there without copying the rest of the cells.

Is there a way to fix this problem?

2) My second problem, I don't know how to incorporate the following If statements to my current codes. Especially the part whether to use Ifs in VBA or insert formula to each cell like I did in one part of my codes. Which one is better in processing time and can you please advise how I can add them to my current codes?

For the comment

'Code for Column C here - Use If Statement

, I need to extract data from "SAP" worksheet, column J using IF statement. How can I convert the following formula to the appropriate VBA codes if it has faster processing time compared to inserting formulas to the cells?

=IF((OR('SAP'!J2="Spare", 'SAP'!J2="Pool",'SAP'!J2="FEP")), "A/M", 'SAP'!J2)

Can you please advise? Thanks a lot! (If you can, please also advise how I can improve my codes. Just a newbie in VBA so any recommendation is appreciated)

 

[JLGWhiz]

Probelm 1.

LastR = wsSAP.Columns("J:J, X:X, AA:AD, AI:AM, AP:AP, AY:AY").Find(What:="*",_
SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

This statement as you have it should include all data in the specified columns. This statement will find the column with the most rows of data, including blanks, so it would not omit any data as you are using it in the code.

Problem 2.

You can copy this and paste it where you have the place marker in the code.

Dim i As Long
    For i = 2 To LastR
        With wsSAP
            If .Range("J" & i) = "Spare" Or .Range("J" & i) = "Pool" Or .Range("J" & i) = "FEP" Then
                wsMaster.Range("C" & i) = "A/M"
            Else
                wsMaster.Range("C" & i) = .Range("J" & i)
            End If
       End With
    Next

This would walk down column J of wsSAP sheet and check for the three values, If one of the values is found it will populate the cell in column C of the corresponding row on wsMaster sheet with "A/M", Otherwise it populates the cell in column C with the value
of the cell from column J of wsSAP. The If statement you expect is imbedded in the For Loop. I don't know which is faster, but with the code you don't have worry about somebody inadvertantly corrupting your formulas.

 

[JackDanIce]

Couldn't figure out what the formula for columns M, AU, AV, AW and AX in Master Worksheet are so left place holders of "no idea", however replace all of your code with below and try:

Sub SubmitToMaster()
    Confirm_Update
    Transfer_Data
End Sub

Private Sub Confirm_Update()
    If MsgBox("Update SAP Data into Master?", vbYesNo + vbQuestion + vbDefaultButton2, "Master McFill") = vbNo Then End
End Sub

Private Sub Transfer_Data()
 
    Dim SAP     As Worksheet: Set SAP = Sheets("SAP")
    Dim wAster  As Worksheet: Set wAster = Sheets("Master Worksheet")
    Dim ColMap  As Variant: ColMap = SAP_Headers
    Dim LR      As Long: LR = LastRow(SAP) - 1
    Dim x       As Long
  
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
          
    With wAster
        .Cells.Value = ""
        For x = LBound(ColMap) + 1 To UBound(ColMap)
            If Left$(ColMap(x), 2) = "@F" Then
                .Cells(2, x).Resize(LR).Formula = Return_Formula(CStr(ColMap(x)), LR)
            Else
                .Cells(2, x).Resize(LR).Value = SAP.Range(CStr(ColMap(x)) & "2").Resize(LR).Value
            End If
        Next x
      
        Add_Additional_Formula wAster, LR
        .Calculate
    End With
  
    With Application
        .EnableEvents = False
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = False
    End With
  
    Set SAP = Nothing: Set wAster = Nothing: Erase ColMap
  
End Sub

Private Function SAP_Headers() As Variant
    SAP_Headers = Split("IgnoreB,AY,C,@F_C,AB,AA,AC,AD,AI,AJ,AK,AL,AP,@F_M,@F_AU,@F_AV,@F_AW,@F_AX", ",")
End Function

Private Function LastRow(ByRef wks As Worksheet)
    With wks
        LastRow = .Cells.Find("*", .Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).row
    End With
End Function

Private Function Return_Formula(ByRef wicked As String, ByRef LR As Long) As String
    Select Case wicked
        Case "@F_C": Return_Formula = "=IF(ISERROR(FIND(SAP!$J2,""Spare|Pool|FEP"")),SAP!$J2,""A/M"")"
        Case "@F_M": Return_Formula = "No idea"
        Case "@F_AU": Return_Formula = "No idea"
        Case "@F_AV": Return_Formula = "No idea"
        Case "@F_AW": Return_Formula = "No idea"
        Case "@F_AX": Return_Formula = "No idea"
    End Select
End Function

Private Sub Add_Additional_Formula(ByRef wks As Worksheet, ByRef LR As Long)
  
    Dim x As Variant
  
    With Application
        .Calculation = xlManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    With wks
        .Cells(2, 17).Resize(LR).Formula = "=IF($N2&""|""&$O2 = ""Podding|Rollup"", ""Podding"",IF($O2=$N2,""Sales/Production"",IF($P2=$O2,""Production"",IF($P2=$N2,""Sales"",""""))))"
        For Each x In Array(21, 25, 29, 33, 37, 41, 45)
            .Cells(2, CLng(x)).Resize(LR).FormulaR1C1 = "=IF(RC[-3]&""|""&RC[-2]=""Podding|Rollup"",""Podding"",IF(OR(RC[-3]=RC[-2],RC[-3]&""|""&RC[-2]=""Shipped|Rollup""),""Sales/Production"",IF(RC[-2]=RC[-1],""Production"",IF(RC[-3]=RC[-1],""Sales"",""""))))"
        Next x
    End With
  
    With Application
        .Calculation = xlAutomatic
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

 

[alisoncleverly]

Probelm 1.

LastR = wsSAP.Columns("J:J, X:X, AA:AD, AI:AM, AP:AP, AY:AY").Find(What:="*",_
SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

This statement as you have it should include all data in the specified columns. This statement will find the column with the most rows of data, including blanks, so it would not omit any data as you are using it in the code.

Problem 2.

You can copy this and paste it where you have the place marker in the code.

Dim i As Long
    For i = 2 To LastR
        With wsSAP
            If .Range("J" & i) = "Spare" Or .Range("J" & i) = "Pool" Or .Range("J" & i) = "FEP" Then
                wsMaster.Range("C" & i) = "A/M"
            Else
                wsMaster.Range("C" & i) = .Range("J" & i)
            End If
       End With
    Next

This would walk down column J of wsSAP sheet and check for the three values, If one of the values is found it will populate the cell in column C of the corresponding row on wsMaster sheet with "A/M", Otherwise it populates the cell in column C with the value
of the cell from column J of wsSAP. The If statement you expect is imbedded in the For Loop. I don't know which is faster, but with the code you don't have worry about somebody inadvertantly corrupting your formulas.

Hi, thank you so much for your explanation and help

 

[JLGWhiz]

Hi, thank you so much for your explanation and help

You're welcome,
regards, JLG

 

[alisoncleverly]

Couldn't figure out what the formula for columns M, AU, AV, AW and AX in Master Worksheet are so left place holders of "no idea", however replace all of your code with below and try:

Sub SubmitToMaster()
    Confirm_Update
    Transfer_Data
End Sub

Private Sub Confirm_Update()
    If MsgBox("Update SAP Data into Master?", vbYesNo + vbQuestion + vbDefaultButton2, "Master McFill") = vbNo Then End
End Sub

Private Sub Transfer_Data()
 
    Dim SAP     As Worksheet: Set SAP = Sheets("SAP")
    Dim wAster  As Worksheet: Set wAster = Sheets("Master Worksheet")
    Dim ColMap  As Variant: ColMap = SAP_Headers
    Dim LR      As Long: LR = LastRow(SAP) - 1
    Dim x       As Long
  
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
          
    With wAster
        .Cells.Value = ""
        For x = LBound(ColMap) + 1 To UBound(ColMap)
            If Left$(ColMap(x), 2) = "@F" Then
                .Cells(2, x).Resize(LR).Formula = Return_Formula(CStr(ColMap(x)), LR)
            Else
                .Cells(2, x).Resize(LR).Value = SAP.Range(CStr(ColMap(x)) & "2").Resize(LR).Value
            End If
        Next x
      
        Add_Additional_Formula wAster, LR
        .Calculate
    End With
  
    With Application
        .EnableEvents = False
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = False
    End With
  
    Set SAP = Nothing: Set wAster = Nothing: Erase ColMap
  
End Sub

Private Function SAP_Headers() As Variant
    SAP_Headers = Split("IgnoreB,AY,C,@F_C,AB,AA,AC,AD,AI,AJ,AK,AL,AP,@F_M,@F_AU,@F_AV,@F_AW,@F_AX", ",")
End Function

Private Function LastRow(ByRef wks As Worksheet)

    With wks
        LastRow = .Cells.Find("*", .Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).row
    End With

End Function

Private Function Return_Formula(ByRef wicked As String, ByRef LR As Long) As String
  
    Select Case wicked
        Case "@F_C": Return_Formula = "=IF(ISERROR(FIND(SAP!$J2,""Spare|Pool|FEP"")),SAP!$J2,""A/M"")"
        Case "@F_M": Return_Formula = "No idea"
        Case "@F_AU": Return_Formula = "No idea"
        Case "@F_AV": Return_Formula = "No idea"
        Case "@F_AW": Return_Formula = "No idea"
        Case "@F_AX": Return_Formula = "No idea"
    End Select
  
End Function

Private Sub Add_Additional_Formula(ByRef wks As Worksheet, ByRef LR As Long)
  
    Dim x As Variant
  
    With Application
        .Calculation = xlManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    With wks
        .Cells(2, 17).Resize(LR).Formula = "=IF($N2&""|""&$O2 = ""Podding|Rollup"", ""Podding"",IF($O2=$N2,""Sales/Production"",IF($P2=$O2,""Production"",IF($P2=$N2,""Sales"",""""))))"
        For Each x In Array(21, 25, 29, 33, 37, 41, 45)
            .Cells(2, CLng(x)).Resize(LR).FormulaR1C1 = "=IF(RC[-3]&""|""&RC[-2]=""Podding|Rollup"",""Podding"",IF(OR(RC[-3]=RC[-2],RC[-3]&""|""&RC[-2]=""Shipped|Rollup""),""Sales/Production"",IF(RC[-2]=RC[-1],""Production"",IF(RC[-3]=RC[-1],""Sales"",""""))))"
        Next x
    End With
  
    With Application
        .Calculation = xlAutomatic
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Hi, thanks a lot for your help.

Here are the formulas in columns M, AU, AV, AW and AX.
Column M: =IF('SAP'!AY2="Unsaleable","Title Transfer",'SAP'!AY2)
Column AU: =IF(OR('SAP'!AM2="0000.00.00",'SAP'!AM2="",'SAP'!AM2="0000-00-00"),"","Shipped")
Column AV: =IF(OR('SAP'!AI2="0000.00.00",'SAP'!AI2="0000-00-00", 'SAP'!AI2=""),"","FPS")
Column AW: ='SAP'!X2
Column AX: =+IF(M2="Title Transfer","x","") (This is directly referenced from Master Worksheet)

Haven't tried the codes you provided yet but will do soon and let you know :D Thanks again!

 

[JackDanIce]

Update just the relevant part, formula above updated:

Private Function Return_Formula(ByRef wicked As String, ByRef LR As Long) As String
    Select Case wicked
        Case "@F_C": Return_Formula = "=IF(ISERROR(FIND(SAP!$J2,""Spare|Pool|FEP"")),SAP!$J2,""A/M"")"
        Case "@F_M": Return_Formula = "=IF(SAP!$AY2=""Unsaleable"",""Title Transfer"",SAP!$AY2)"
        Case "@F_AU": Return_Formula = "=IF(SAP!$AM2*2 = 0,"""",""Shipped"")"
        Case "@F_AV": Return_Formula = "=IF(SAP!$AI2*2 = 0,"""",""FPS"")"
        Case "@F_AW": Return_Formula = "=SAP!$X2"
        Case "@F_AX": Return_Formula = "=IF($M2=""Title Transfer"",""x"","""")"
    End Select    
End Function

 

[alisoncleverly]

Couldn't figure out what the formula for columns M, AU, AV, AW and AX in Master Worksheet are so left place holders of "no idea", however replace all of your code with below and try:

Sub SubmitToMaster()
    Confirm_Update
    Transfer_Data
End Sub

Private Sub Confirm_Update()
    If MsgBox("Update SAP Data into Master?", vbYesNo + vbQuestion + vbDefaultButton2, "Master McFill") = vbNo Then End
End Sub

Private Sub Transfer_Data()

    Dim SAP     As Worksheet: Set SAP = Sheets("SAP")
    Dim wAster  As Worksheet: Set wAster = Sheets("Master Worksheet")
    Dim ColMap  As Variant: ColMap = SAP_Headers
    Dim LR      As Long: LR = LastRow(SAP) - 1
    Dim x       As Long
 
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
         
    With wAster
        .Cells.Value = ""
        For x = LBound(ColMap) + 1 To UBound(ColMap)
            If Left$(ColMap(x), 2) = "@F" Then
                .Cells(2, x).Resize(LR).Formula = Return_Formula(CStr(ColMap(x)), LR)
            Else
                .Cells(2, x).Resize(LR).Value = SAP.Range(CStr(ColMap(x)) & "2").Resize(LR).Value
            End If
        Next x
     
        Add_Additional_Formula wAster, LR
        .Calculate
    End With
 
    With Application
        .EnableEvents = False
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = False
    End With
 
    Set SAP = Nothing: Set wAster = Nothing: Erase ColMap
 
End Sub

Private Function SAP_Headers() As Variant
    SAP_Headers = Split("IgnoreB,AY,C,@F_C,AB,AA,AC,AD,AI,AJ,AK,AL,AP,@F_M,@F_AU,@F_AV,@F_AW,@F_AX", ",")
End Function

Private Function LastRow(ByRef wks As Worksheet)
    With wks
        LastRow = .Cells.Find("*", .Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).row
    End With
End Function

Private Function Return_Formula(ByRef wicked As String, ByRef LR As Long) As String
    Select Case wicked
        Case "@F_C": Return_Formula = "=IF(ISERROR(FIND(SAP!$J2,""Spare|Pool|FEP"")),SAP!$J2,""A/M"")"
        Case "@F_M": Return_Formula = "No idea"
        Case "@F_AU": Return_Formula = "No idea"
        Case "@F_AV": Return_Formula = "No idea"
        Case "@F_AW": Return_Formula = "No idea"
        Case "@F_AX": Return_Formula = "No idea"
    End Select
End Function

Private Sub Add_Additional_Formula(ByRef wks As Worksheet, ByRef LR As Long)
 
    Dim x As Variant
 
    With Application
        .Calculation = xlManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    With wks
        .Cells(2, 17).Resize(LR).Formula = "=IF($N2&""|""&$O2 = ""Podding|Rollup"", ""Podding"",IF($O2=$N2,""Sales/Production"",IF($P2=$O2,""Production"",IF($P2=$N2,""Sales"",""""))))"
        For Each x In Array(21, 25, 29, 33, 37, 41, 45)
            .Cells(2, CLng(x)).Resize(LR).FormulaR1C1 = "=IF(RC[-3]&""|""&RC[-2]=""Podding|Rollup"",""Podding"",IF(OR(RC[-3]=RC[-2],RC[-3]&""|""&RC[-2]=""Shipped|Rollup""),""Sales/Production"",IF(RC[-2]=RC[-1],""Production"",IF(RC[-3]=RC[-1],""Sales"",""""))))"
        Next x
    End With
 
    With Application
        .Calculation = xlAutomatic
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Hi @JackDanIce, I tried your code but got an error message saying LastRow is not defined

Dim LR      As Long: LR = LastRow(SAP) - 1

Not sure how you would define that variable.

Can you please advise again? Thank you!

 

[JackDanIce]

Can you check you copied all of the suggested code please? I can see this part which should provide for LastRow:

Private Function LastRow(ByRef wks As Worksheet)
    With wks
        LastRow = .Cells.Find("*", .Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).row
    End With
End Function

Testing with two sheets named Master Worksheet and SAP, with dummy data to approximate your structure, I didn't get that error...

Just re-tested and no errors, but I may not have mimic'd your spreadsheet as you have described it.

 

[alisoncleverly]

Can you check you copied all of the suggested code please? I can see this part which should provide for LastRow:

Private Function LastRow(ByRef wks As Worksheet)
    With wks
        LastRow = .Cells.Find("*", .Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).row
    End With
End Function

Testing with two sheets named Master Worksheet and SAP, with dummy data to approximate your structure, I didn't get that error...

Just re-tested and no errors, but I may not have mimic'd your spreadsheet as you have described it.

Sorry for earlier post. It was my mistake. But this time I think it must be me not describing the data structure correctly. Couldn't upload any sample file from my laptop so took a bit of time to replicate one from my personal laptop.

Here is the link to sample file. In my sample file, there is a sheet called "Master Worksheet (Original)", I left it there because you might want to see the original column order.

In the original post, I noted down that I have 3 different sheets to do the job: Master Worksheet, Converted, and SAP. Now I wanted to reduce to just work with Master Worksheet and SAP. And that Converted tab shares the exact same column order/headers with Master Worksheet.

Your codes did all the copy and convert thing (thanks again for that) but here are a few things I noticed:

1. The headers in my "Master Worksheet" became blank when I ran the macro.

2. The formulas were supposed to return in these columns AU, AV, AW, AX but instead they were in columns N, O, P, Q my the original formulas were gone because of them.
Also, I think they did not return correct results.

3. Before, I had this piece of codes to return in each Day column the value per the IF statements. For some reason, after pasting your codes, it no longer worked. When I entered values in Sales and Production cells, the Day cell was still blank and Status cell didn't evaluation correctly.
(From columns R - AS, there're 8 Day groups. Each group has these 4 columns: Sales, Production, Day #, Status)

Public Sub MasterChange(SPD As Range)
    Dim rSales As Range
    Dim rProduction As Range
    Dim rDay As Range
    
    Set rSales = SPD.Cells(1, 1)
    Set rProduction = SPD.Cells(1, 2)
    Set rDay = SPD.Cells(1, 3)
    
    Application.EnableEvents = False
    If rSales = "Rollup" And rProduction = "Rollup" Then
        rDay = "Rollup"
    ElseIf rSales = "Rollup" And rProduction = "Green" Then
        rDay = "Green"
    ElseIf rSales = "Rollup" And rProduction = "Yellow" Then
        rDay = "Yellow"
    ElseIf rSales = "Rollup" And rProduction = "Red" Then
        rDay = "Red"
    End If
    Application.EnableEvents = True
End Sub

For those columns with formula, would the "|" be the reason here?

Can you please again advise? Sorry for the very long notes and thanks so much for your help so far. Really appreciate it!