CODEWITHSUNDEEP
vbaloopsexcel
Tony
Tony

Reputation: 11

VBA Loop to Copy Columns

I want to copy a defined number (lets say 10) of rows from one sheet ("Data") and paste it in another sheet ("Input). This will cause a bunch of stuff to calculate. Then I want to copy said calculated data (6 rows) from ("Input") to ("Data") and paste in a results table. THen I would repeat this a defined number of times for a certain number of columns (lets say 10).

I tried writing the code but it has literally been years since I have written code.

I used the Record Marco thing and got this:

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("C5:C14").Select
    Selection.Copy
    Sheets("Input").Select
    Range("C5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("P12:P19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("C22").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D5:D14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Input").Select
    Range("C5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("P12:P19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("D22").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G16").Select
End Sub

I hope this makes sense

Upvotes: 0

Views: 1130

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

Sub Macro2()

    Const NUM_TIMES As Long = 10
    Dim shtInput As Worksheet, shtData As Worksheet
    Dim rngCopy As Range, i As Long

    Set shtInput = Sheets("Input")
    Set shtData = Sheets("Data")

    Set rngCopy = shtData.Range("C5:C15")

    For i = 1 To NUM_TIMES

        With shtInput
            .Range("C5").Resize(rngCopy.Rows.Count, 1).Value = rngCopy.Value
            .Calculate
            rngCopy(1).Offset(17, 0).Resize(8, 1).Value = .Range("P12:P19").Value
        End With

        Set rngCopy = rngCopy.Offset(0, 1)
    Next i

End Sub

Upvotes: 1

Related Questions