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)

 

[alisoncleverly]

Sorry for the link provided earlier, it no longer works for some reason.

Here is the new one. Please let me know if it's still not accessible. Thank you!

 

[JackDanIce]

Hi Alison,

You've sent the file where the macro over-wrote existing formula, so I'm unable to re-insert these (e.g. columns N:Q on sheet Master)

I've re-started with file below, as there was lots of other code in yours I wasn't sure was doing what.

Dropbox - Error - Simplify your life

www.dropbox.com

What you need to do:
Add headers back into sheet Master
In row 2 of sheet Master, enter the exact formula you need, in the columns you require

What the code does: (attached to button in Master)
Clears any values in rows 3 or below in Master (I'm keeping row 2 so that formulas can be reused each time SAP data is transferred)
Copies only specific columns with data from SAP into Master, starting at row 2 (this should not overwrite the formula in row 2, Master)
Loops across row 2 from column 1 (A) to the last column, any cell containing a formula, it drags to the last row in that column only, then moves to next cell containing a formula

This assumes:
You will always have at least 1 row of data in SAP to transfer into Master
You edit any required formula in row 2 of Master and the code does not need to insert any formula for you

Give this a try and see if it helps progress.
Jack

 

[alisoncleverly]

Hi Alison,

You've sent the file where the macro over-wrote existing formula, so I'm unable to re-insert these (e.g. columns N:Q on sheet Master)

I've re-started with file below, as there was lots of other code in yours I wasn't sure was doing what.

Dropbox - Error - Simplify your life

www.dropbox.com

What you need to do:
Add headers back into sheet Master
In row 2 of sheet Master, enter the exact formula you need, in the columns you require

What the code does: (attached to button in Master)
Clears any values in rows 3 or below in Master (I'm keeping row 2 so that formulas can be reused each time SAP data is transferred)
Copies only specific columns with data from SAP into Master, starting at row 2 (this should not overwrite the formula in row 2, Master)
Loops across row 2 from column 1 (A) to the last column, any cell containing a formula, it drags to the last row in that column only, then moves to next cell containing a formula

This assumes:
You will always have at least 1 row of data in SAP to transfer into Master
You edit any required formula in row 2 of Master and the code does not need to insert any formula for you

Give this a try and see if it helps progress.
Jack

Hi Jack, thanks a lot! It works on my side now.
Thanks again and stay safe!

 

[JackDanIce]

If the issue is resolved, then fantastic! TY, stay safe too and good luck with further development