Hello,
I am trying to consolidate 4 worksheets into 1. The problem I am running into is that I am trying to add values to cells based on what is ported over from a specific sheet, when I try it the values I am adding just overwrite each other.
For instance, I have 2 sheets:
Delete:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]NDC[/TD]
[/TR]
[TR]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]24338002010[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]24338002110[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]31722020090[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]31722020190[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]31722020290[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Add:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]NDC[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]00168013915[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]00168013930[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]00168013960[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]45802058001[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
What I want on the consolidated sheet:
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Action[/TD]
[TD]NDC[/TD]
[/TR]
[TR]
[TD]DEL[/TD]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]24338002010[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]DEL[/TD]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]24338002110[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]DEL[/TD]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]31722020090[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]DEL[/TD]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]31722020190[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]DEL[/TD]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]31722020290[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]ADD[/TD]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]00168013915[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]ADD[/TD]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]00168013930[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]ADD[/TD]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]00168013960[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]ADD[/TD]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]45802058001[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
I am trying to consolidate 4 worksheets into 1. The problem I am running into is that I am trying to add values to cells based on what is ported over from a specific sheet, when I try it the values I am adding just overwrite each other.
Code:
Sub FN_Upload()
Dim wksDst As Worksheet
Dim rngSrc As Range, rngDst As Range
Dim SrcLR As Long, DstLR As Long, i As Long
Dim tmp As String
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set wksDst = Sheets("FN_Upload")
DstLR = wksDst.Range("D" & Rows.Count).End(xlUp).Row
Set rngDst = wksDst.Cells(DstLR + 1, 4)
With Sheets("Add NNPD")
SrcLR = .Range("A" & Rows.Count).End(xlUp).Row
Set rngSrc = .Range(.Cells(2, 1), .Cells(SrcLR, 1))
For i = 1 To SrcLR
rngSrc.Copy Destination:=rngDst
Next i
End With
For i = 1 To DstLR
If rngDst > 0 Then
wksDst.Range("A" & i + 1).Value = "ADD"
wksDst.Range("B" & i + 1).Value = 10488
wksDst.Range("C" & i + 1).Value = 3
wksDst.Range("E" & i + 1).Value = 1
wksDst.Range("F" & i + 1).Value = 3
End If
Next i
DstLR = wksDst.Range("D" & Rows.Count).End(xlUp).Row
Set rngDst = wksDst.Cells(DstLR + 1, 4)
With Sheets("Add NPDL")
SrcLR = .Range("A" & Rows.Count).End(xlUp).Row
Set rngSrc = .Range(.Cells(2, 1), .Cells(SrcLR, 1))
For i = 1 To SrcLR
rngSrc.Copy Destination:=rngDst
Next i
End With
For i = 1 To DstLR
If rngDst > 0 Then
wksDst.Range("A" & i + 1).Value = "ADD"
wksDst.Range("B" & i + 1).Value = 10488
wksDst.Range("C" & i + 1).Value = 3
wksDst.Range("E" & i + 1).Value = 1
wksDst.Range("F" & i + 1).Value = 2
End If
Next i
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
For instance, I have 2 sheets:
Delete:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]NDC[/TD]
[/TR]
[TR]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]24338002010[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]24338002110[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]31722020090[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]31722020190[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]31722020290[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Add:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]NDC[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]00168013915[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]00168013930[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]00168013960[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]45802058001[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
What I want on the consolidated sheet:
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Action[/TD]
[TD]NDC[/TD]
[/TR]
[TR]
[TD]DEL[/TD]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]24338002010[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]DEL[/TD]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]24338002110[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]DEL[/TD]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]31722020090[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]DEL[/TD]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]31722020190[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]DEL[/TD]
[TD][TABLE="width: 137"]
<tbody>[TR]
[TD]31722020290[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]ADD[/TD]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]00168013915[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]ADD[/TD]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]00168013930[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]ADD[/TD]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]00168013960[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]ADD[/TD]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD]45802058001[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]