CODEWITHSUNDEEP
vbaexcel
Joseph G
Joseph G

Reputation: 1

Code for repeating a macro

I need to repeat a macro called "DTest" for a given range in a source spreadsheet which has 390 rows. "DTest" macro creates a new Excel file and transpose pastes the range and saves the file name with value in cell "B2" in a given path. 

Sub RepeatDTest()

    Range("1:1,2:2").Select
    Selection.Copy
    DTest
    Range("1:1,3:3").Select
    Selection.Copy
    DTest
    Range("1:1,4:4").Select
    Selection.Copy
    DTest
End Sub

My range includes row 1 as header, and rows from 2 to 390 as content in the new spreadsheet's row 2.

How to write a code for Range ("1:1,2:2"), Range ("1:1,3:3")....... Range ("1:1,390:390")?

Upvotes: 0

Views: 154

Answers (1)

Vegard
Vegard

Reputation: 4917

Sub RepeatTestD()
    ' For every value of i, from 2 until 390, run the following lines of code
    For i = 2 To 390
        ' No reason to do .Select, we can perform .Copy directly on the range. 
        ' This is more precise and is also a much better coding practice.
        ' Using i as an argument in the range reference is the central piece.
        ' For i = 2, the macro will copy the range "1:1","2:2". 
        ' For i = 10, it will be "1:1", "10:10". Ad "i-finitum".
        Range("1:1", i & ":" & i).Copy
        Call DTest
    ' Go to the next value of i and go back to the start of the loop
    Next i
End Sub

Upvotes: 1

Related Questions