Code for repeating a macro

Question

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")?

Vegard · Accepted Answer · 2015-10-01 10:01:00Z

1

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

edited Oct 1, 2015 at 10:01

answered Oct 1, 2015 at 9:35

Vegard

5,2472 gold badges25 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Vegard Over a year ago

So you are asking about the macro DTest, not how to call it?

Joseph G Over a year ago

I have a source spreadsheet with number of rows, now I would like to do the following, # copy entire rows 1, 2.. create a new spreadsheet (new excel file) and transpose paste and save the file with value in cell “B2” of new spreadsheet. Again # copy entire rows 1, 3.. create a new spreadsheet (new excel file) and transpose paste and save the file with value in cell “B2” of new spreadsheet. # copy entire rows 1, 4.. create a new spreadsheet (new excel file) and transpose paste and save the file with value in cell “B2” of new spreadsheet. I recorded DTest macro, now I need to run for 390 rows.

Vegard Over a year ago

Yes... if you have the macro that makes the new file and all that stuff, this macro will run it for 390 rows. Just change Call Dtest into Call whateverYourMacroNameIs

Joseph G Over a year ago

%ActiveWorkbook.SaveAs Filename:= _ "G:\DASS\3 Data\Test\" & Range("B2").Value & ".xlsx", FileFormat _ :=xlOpenXMLWorkbook, CreateBackup:=False ActiveWindow.Close'% I get an error at this point for the second file while running the macro. Thanx

Vegard Over a year ago

You should probably make a new question for that... but here are two pointers: (1) what error are you getting? (2) looks like you are trying to save several files with the same file name (value in B2)??

Collectives™ on Stack Overflow

Code for repeating a macro

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related