Vba code to add and copy data needed

[kelly mort]

I have three data tables. Two of them are sitting on a single sheet and the other is on a different sheet.

I am adding from the two tables from the single sheet and pasting the result to the Sheet2 table.

Table 1 starts from G4 to O104 and Table 2 starts from P4 to X104.


Then table 3 starts from D4 to L104.(This is where I am adding to)

This is how I want to add:

Add G4 and P4 and place result into D4
Then H4 and Q4 and place result into E4 , etc

Thanks for helping
Kelly

 

[kelly mort]

So I have tried to come up with this code that is doing what I want to do but I left with how to loop it with a for loop maybe. If there is a better and fast way please learn me know:

Sub CopyAdd()
    Dim Ms As Worksheet, Cs As Worksheet
    Set Ms = Sheet3
    Set Cs = Sheet4
    With Ms
    .Range("D4").Value = Application.Sum(Cs.Range("G7").Value, Application.RoundUp(Cs.Range("P7").Value * 0.5, 0))
    
    .Range("E4").Value = Application.Sum(Cs.Range("H7").Value, Application.RoundUp(Cs.Range("Q7").Value * 0.5, 0))
    .Range("F4").Value = Application.Sum(Cs.Range("I7").Value, Application.RoundUp(Cs.Range("R7").Value * 0.5, 0))
    .Range("G4").Value = Application.Sum(Cs.Range("J7").Value, Application.RoundUp(Cs.Range("S7").Value * 0.5, 0))
    .Range("H4").Value = Application.Sum(Cs.Range("K7").Value, Application.RoundUp(Cs.Range("T7").Value * 0.5, 0))
    .Range("I4").Value = Application.Sum(Cs.Range("L7").Value, Application.RoundUp(Cs.Range("U7").Value * 0.5, 0))
    .Range("J4").Value = Application.Sum(Cs.Range("M7").Value, Application.RoundUp(Cs.Range("V7").Value * 0.5, 0))
    .Range("K4").Value = Application.Sum(Cs.Range("N7").Value, Application.RoundUp(Cs.Range("W7").Value * 0.5, 0))
    .Range("L4").Value = Application.Sum(Cs.Range("O7").Value, Application.RoundUp(Cs.Range("X7").Value * 0.5, 0))
    
    '.Range(.Cells(7, 2), .Cells(107, 28))
    
    '.Range("M4").Value = Application.Sum(Ms.Range("D4:L4").Value)
    Ms.Range(.Cells(4, 13)).Value = Application.Sum(Ms.Range(.Cells(4, 4), .Cells(4, 12)).Value)
    End With
    
End Sub

I wanna use the “.cells” so that I can employ some variables in a loop to get workdone but it seems I am getting stacked with it. I am not that cool with this trick so I need seniors here pull me out:

    Ms.Range(.Cells(4, 13)).Value = Application.Sum(Ms.Range(.Cells(4, 4), .Cells(4, 12)).Value)

 

[MrIfOnly]

Hi Kelly:

Try this (change the value of "x To" to reflect how ever many rows you wish to copy/add minus one):

Sub CopyAdd()
    Dim Ms As Worksheet, Cs As Worksheet
    Dim x As Integer, y As Integer
    Set Ms = ActiveWorkbook.Worksheets("Sheet3")
    Set Cs = ActiveWorkbook.Worksheets("Sheet4")
    With Cs
        For x = 0 To 10
            For y = 0 To 8
                Ms.Range("D4").Offset(x, y).Value = Application.Sum(.Range("G7").Offset(x, y).Value, Application.RoundUp(.Range("P7").Offset(x, y).Value * 0.5, 0))
            Next y
        Next x
    End With
End Sub

You could use a similar method using the .cells command, but I find it easier to read and edit A1 reference style as in the above code.

Regards,

CJ

 

[kelly mort]

Hello CJ,

It worked perfectly
The only issue I had is I tried this :

For X = 0 To .Range ("B1007").End (xlUp).Row - 1

It still copies 10 rows. I know I am doing something wrongly buy can't figure it out

 

[MrIfOnly]

Try this:

Sub CopyAdd()
    Dim Ms As Worksheet, Cs As Worksheet
    Dim x As Integer, y As Integer
    Dim lRow As Long
    Set Ms = ActiveWorkbook.Worksheets("Sheet3")
    Set Cs = ActiveWorkbook.Worksheets("Sheet4")
    With Cs
        lRow = .Range("G7").End(xlDown).Row - 7
        For x = 0 To lRow
            For y = 0 To 8
                Ms.Range("D4").Offset(x, y).Value = Application.Sum(.Range("G7").Offset(x, y).Value, Application.RoundUp(.Range("P7").Offset(x, y).Value * 0.5, 0))
            Next y
        Next x
    End With
End Sub

Note that I am using minus 7 for lRow because we are starting in G7. If that starting cell changes, adjust accordingly.

Regards,

CJ

 

[kelly mort]

Sub CopyAdd()
Dim Ms As Worksheet, Cs As Worksheet
Dim x As Integer, y As Integer
Dim lRow As Long
Set Ms = ActiveWorkbook.Worksheets("Sheet3")
Set Cs = ActiveWorkbook.Worksheets("Sheet4")
With Cs
lRow = .Range("G7").End(xlDown).Row - 7
For x = 0 To lRow
For y = 0 To 8
Ms.Range("D4").Offset(x, y).Value = Application.Sum(.Range("G7").Offset(x, y).Value, Application.RoundUp(.Range("P7").Offset(x, y).Value * 0.5, 0))
Next y
Ms.Range("N7").Offset(x, 0).Value = Application.Sum(.Range(Cells (x+7, 4), Cells(x+7, 13)).Value)
Next x
End With
End Sub

I updated the code like this and when I call it from the userform, I get an error meanwhile I get no errors when I run it from the module. Any reason why?

 

[MrIfOnly]

What is the error you are getting? Please post the code from your userform that you are using to call it.