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:
<tbody>
</tbody>
Add:
<tbody>
</tbody>
What I want on the consolidated sheet:
<tbody>
</tbody>
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:
NDC | |
<tbody> </tbody><colgroup><col></colgroup> | |
<tbody> </tbody><colgroup><col></colgroup> | |
<tbody> </tbody><colgroup><col></colgroup> | |
<tbody> </tbody><colgroup><col></colgroup> | |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody>
</tbody>
Add:
<tbody> </tbody><colgroup><col></colgroup> | |
<tbody> </tbody><colgroup><col></colgroup> | |
<tbody> </tbody><colgroup><col></colgroup> | |
<tbody> </tbody><colgroup><col></colgroup> | |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody>
</tbody>
What I want on the consolidated sheet:
Action | NDC | |
DEL |
<tbody> </tbody><colgroup><col></colgroup> | |
DEL |
<tbody> </tbody><colgroup><col></colgroup> | |
DEL |
<tbody> </tbody><colgroup><col></colgroup> | |
DEL |
<tbody> </tbody><colgroup><col></colgroup> | |
DEL |
<tbody> </tbody><colgroup><col></colgroup> | |
ADD |
<tbody> </tbody><colgroup><col></colgroup> | |
ADD |
<tbody> </tbody><colgroup><col></colgroup> | |
ADD |
<tbody> </tbody><colgroup><col></colgroup> | |
ADD |
<tbody> </tbody><colgroup><col></colgroup> |
<tbody>
</tbody>