VBA to copy and convert data to a different sheet

alisoncleverly

New Member
Joined
Feb 20, 2020
Messages
28
Office Version
  1. 2013
Platform
  1. Windows
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:

VBA Code:
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
VBA Code:
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
VBA Code:
'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)
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Probelm 1.
Code:
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.
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.
 
Last edited:

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,723
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
Feb 20, 2020
Messages
28
Office Version
  1. 2013
Platform
  1. Windows
Probelm 1.
Code:
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.
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 :)
 

alisoncleverly

New Member
Joined
Feb 20, 2020
Messages
28
Office Version
  1. 2013
Platform
  1. Windows
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:
VBA Code:
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,723
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Update just the relevant part, formula above updated:
VBA Code:
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

New Member
Joined
Feb 20, 2020
Messages
28
Office Version
  1. 2013
Platform
  1. Windows
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:
VBA Code:
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

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

Not sure how you would define that variable.

Can you please advise again? Thank you!
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,723
Office Version
  1. 365
Platform
  1. Windows
Can you check you copied all of the suggested code please? I can see this part which should provide for LastRow:
VBA Code:
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

New Member
Joined
Feb 20, 2020
Messages
28
Office Version
  1. 2013
Platform
  1. Windows
Can you check you copied all of the suggested code please? I can see this part which should provide for LastRow:
VBA Code:
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)

VBA Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,402
Members
416,026
Latest member
melvic69

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top