(Solved) – Copy & Convert Data from Worksheet A to Worksheet B


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
'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’m having trouble not knowing how to incorporating 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, 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)

Leave a Reply

Your email address will not be published. Required fields are marked *