Option Explicit
Sub Upload2()
Dim sh As Worksheet
Dim LstRw As Long
Dim Rng As Range, c As Range
Dim y As String, T As String, H As String, x As Range, ofset As Long
Dim lRowToCopy As Long
y = "Yes"
T = "TP"
H = "Home"
'Go to recommendations sheet
Set sh = Sheets("Recommendations")
With sh
'Identify container changes in column CH
LstRw = .Cells(.Rows.Count, "CH").End(xlUp).Row
Set Rng = .Range("CH2:CH" & LstRw)
For Each c In Rng.Cells
'If there is a container change and it is TP
If c = y And (c.Offset(0, 14) = T Or c.Offset(0, 14) = H) Then
'Go to Services Export Raw and find same SID in column M and insert 2 rows underneath last like SID
Set x = Sheets("Services Export Raw").Range("M:M").Find(what:=c.Offset(, -81).Value, LookAt:=xlWhole, LookIn:=xlFormulas, searchformat:=False)
If Not x Is Nothing Then
ofset = 1
Do Until x.Offset(ofset) <> c.Offset(, -81).Value
ofset = ofset + 1
Loop
x.Offset(ofset).EntireRow.Insert
'Extra line inserted if Home
If c.Offset(0, 14) = H Then x.Offset(ofset).EntireRow.Insert
'Copy data
'At this line, the value in CH is y (Yes) and the value in CV is either T (TP) or H (Home)
lRowToCopy = x.Row
Select Case c.Offset(0, 14).Value
Case T 'copy certain cells to the single inserted row
'B, C, D, E, F, G, H, I, J, K, M, S, BH, BI, BJ, BK, BM, BS, BV
With Sheets("Services Export Raw")
.Range(.Cells(lRowToCopy, "B"), .Cells(lRowToCopy, "K")).Copy _
Destination:=.Cells(lRowToCopy + 1, "B")
.Range(.Cells(lRowToCopy, "M"), .Cells(lRowToCopy, "M")).Copy _
Destination:=.Cells(lRowToCopy + 1, "M")
.Range(.Cells(lRowToCopy, "S"), .Cells(lRowToCopy, "S")).Copy _
Destination:=.Cells(lRowToCopy + 1, "S")
.Cells(lRowToCopy + 1, "T").Value = "SWO"
.Cells(lRowToCopy + 1, "U").Value = "PERM"
.Cells(lRowToCopy + 1, "V").Value = "OC"
'Place holder for copying corresponding value in column BH into column W on 'Recommendations' tab
'Place holder for column X
'Place holder for column Y
'Place holder for column Z
'Place holder for column AB
'Place holder for column AC
'Place holder for column AD
.Cells(lRowToCopy + 1, "AH").Resize(1, 9).Value = "0"
.Cells(lRowToCopy + 1, "AQ").Value = "PER"
.Cells(lRowToCopy + 1, "AR").Value = "EV"
'Place holder for column AS
.Cells(lRowToCopy + 1, "AT").Value = "0"
.Cells(lRowToCopy + 1, "AV").Value = "PER"
.Cells(lRowToCopy + 1, "AW").Value = "EV"
'Place holder for column AX
.Cells(lRowToCopy + 1, "AY").Value = "0"
.Range(.Cells(lRowToCopy, "BH"), .Cells(lRowToCopy, "BK")).Copy _
Destination:=.Cells(lRowToCopy + 1, "BH")
.Range(.Cells(lRowToCopy, "BM"), .Cells(lRowToCopy, "BM")).Copy _
Destination:=.Cells(lRowToCopy + 1, "BM")
.Range(.Cells(lRowToCopy, "BS"), .Cells(lRowToCopy, "BS")).Copy _
Destination:=.Cells(lRowToCopy + 1, "BS")
.Range(.Cells(lRowToCopy, "BV"), .Cells(lRowToCopy, "BV")).Copy _
Destination:=.Cells(lRowToCopy + 1, "BV")
'Place holder for column CL
'Place holder for column CM
'Place holder for column CN
'Place holder for column CO
'Place holder for column CP
.Cells(lRowToCopy + 1, "CQ").Value = "0"
.Cells(lRowToCopy + 1, "CR").Value = "DIS"
End With
Case H 'copy certain cells to the two inserted rows
'B, C, D, E, F, G, H, I, J, K, M, S, BH, BI, BJ, BK, BM, BS, BV, BY, BZ, CD, CE, CF, CG, CH, CI
With Sheets("Services Export Raw")
.Range(.Cells(lRowToCopy, "B"), .Cells(lRowToCopy, "K")).Copy _
Destination:=.Range(.Cells(lRowToCopy + 1, "B"), .Cells(lRowToCopy + 2, "B"))
.Range(.Cells(lRowToCopy, "M"), .Cells(lRowToCopy, "M")).Copy _
Destination:=.Range(.Cells(lRowToCopy + 1, "M"), .Cells(lRowToCopy + 2, "M"))
.Range(.Cells(lRowToCopy, "S"), .Cells(lRowToCopy, "S")).Copy _
Destination:=.Range(.Cells(lRowToCopy + 1, "S"), .Cells(lRowToCopy + 2, "S"))
.Cells(lRowToCopy + 1, "T").Value = "DLV"
.Cells(lRowToCopy + 2, "T").Value = "REM"
.Cells(lRowToCopy + 1, "U").Value = "PERM"
.Cells(lRowToCopy + 2, "U").Value = "PERM"
.Cells(lRowToCopy + 1, "V").Value = "OC"
.Cells(lRowToCopy + 2, "V").Value = "OC"
'Place holder for column W
'Place holder for column W
'Place holder for column X
'Place holder for column X
'Place holder for column Y
'Place holder for column Y
'Place holder for column Z
'Place holder for column Z
'Place holder for column AB
'Place holder for column AB
'Place holder for column AC
'Place holder for column AC
'Place holder for column AD
'Place holder for column AD
.Cells(lRowToCopy + 1, "AH").Resize(1, 9).Value = "0"
.Cells(lRowToCopy + 2, "AH").Resize(1, 9).Value = "0"
.Cells(lRowToCopy + 1, "AQ").Value = "PER"
.Cells(lRowToCopy + 2, "AQ").Value = "PER"
.Cells(lRowToCopy + 1, "AR").Value = "EV"
.Cells(lRowToCopy + 2, "AR").Value = "EV"
'Place holder for column AS
'Place holder for column AS
.Cells(lRowToCopy + 1, "AT").Value = "0"
.Cells(lRowToCopy + 2, "AT").Value = "0"
.Cells(lRowToCopy + 1, "AV").Value = "PER"
.Cells(lRowToCopy + 2, "AV").Value = "PER"
.Cells(lRowToCopy + 1, "AW").Value = "EV"
.Cells(lRowToCopy + 2, "AW").Value = "EV"
'Place holder for column AX
'Place holder for column AX
.Range(.Cells(lRowToCopy, "BH"), .Cells(lRowToCopy, "BK")).Copy _
Destination:=.Range(.Cells(lRowToCopy + 1, "BH"), .Cells(lRowToCopy + 2, "BK"))
.Range(.Cells(lRowToCopy, "BM"), .Cells(lRowToCopy, "BM")).Copy _
Destination:=.Range(.Cells(lRowToCopy + 1, "BM"), .Cells(lRowToCopy + 2, "BM"))
.Range(.Cells(lRowToCopy, "BS"), .Cells(lRowToCopy, "BS")).Copy _
Destination:=.Range(.Cells(lRowToCopy + 1, "BS"), .Cells(lRowToCopy + 2, "BS"))
.Range(.Cells(lRowToCopy, "BV"), .Cells(lRowToCopy, "BV")).Copy _
Destination:=.Range(.Cells(lRowToCopy + 1, "BV"), .Cells(lRowToCopy + 2, "BV"))
.Range(.Cells(lRowToCopy, "BY"), .Cells(lRowToCopy, "BZ")).Copy _
Destination:=.Range(.Cells(lRowToCopy + 1, "BY"), .Cells(lRowToCopy + 2, "BZ"))
.Range(.Cells(lRowToCopy, "CD"), .Cells(lRowToCopy, "CI")).Copy _
Destination:=.Range(.Cells(lRowToCopy + 1, "CD"), .Cells(lRowToCopy + 2, "CI"))
'Place holder for column CL
'Place holder for column CL
'Place holder for column CM
'Place holder for column CM
'Place holder for column CN
'Place holder for column CN
'Place holder for column CO
'Place holder for column CO
'Place holder for column CP
'Place holder for column CP
.Cells(lRowToCopy + 1, "CQ").Value = "0"
.Cells(lRowToCopy + 2, "CQ").Value = "0"
.Cells(lRowToCopy + 1, "CR").Value = "DIS"
.Cells(lRowToCopy + 2, "CR").Value = "DIS"
End With
Case Else
Stop ' should not get here
End Select
End If
End If
Next c
End With
End Sub
My question is where I say 'Place holder for copying corresponding value in column BH into column W on 'Recommendations' tab towards the top of my code, do I have to build in more defined ranges or is there an easier way to build this into my code?
Reply With Quote