CODEWITHSUNDEEP
excelvba
Joe Keenan
Joe Keenan

Reputation: 1

Copying from 1 Worksheet to another, To another! VBA

Very basic VBA user trying to learn!

I've got 3 worksheets - Input, Calc, Output.

What i need to do is take the data in the Input table, (dynamic number of rows) copy the row into the calc sheet, then take the results on the calc, copy them into the Output sheet- then move on to the next row in the Input sheet, copy them into the Calc sheet again (this can be overwritten each time) and then copy the results into the Output sheet on the next row.

So I'll end up with the same amount of Rows on the Output sheet, as i start with on the Input file.

This is what i have so far which works for 1 row of course, but I'm not sure how I get it to work for the next row.

Sub sbCopyRangeToAnotherSheet()
Sheets("Input").Range("A2:E2").Copy Destination:=Sheets("Calc").Range("B9")

Application.CutCopyMode = False

Sheets("Calc").Range("B42:K42").Copy
Sheets("Calc").Range("B43:K43").PasteSpecial xlPasteValues

Application.CutCopyMode = False

Sheets("Calc").Range("B43:K43").Copy Destination:=Sheets("Output").Range("A2")

Application.CutCopyMode = False

End Sub

Upvotes: 0

Views: 48

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

For example:

Sub sbCopyRangeToAnotherSheet()

    Dim rw As Range, wsCalc As Worksheet, cOut As Range
    
    Set wsCalc = Sheets("Calc")
    
    Set rw = Sheets("Input").Range("A2:E2") 'first input row
    Set cOut = Sheets("Output").Cells(Rows.Count, "A").Offset(1, 0)
    
    Do While Application.CountA(rw) > 0                        'loop while have input data
        wsCalc.Range("B9:F9").Value = rw.Value                 'copy input values
        c.Resize(1, 10).Value = wsCalc.Range("B42:K42").Value  'copy output values
        
        Set rw = rw.Offset(1, 0) 'next input row
        Set c = c.Offset(1, 0)   'next output position
    Loop
    
End Sub

Upvotes: 1

Related Questions