CODEWITHSUNDEEP
excelvba
Joseph Botros
Joseph Botros

Reputation: 31

Loop through Excel Macro

Hi I recorded this simple Macro, which basically save a copy of excel sheet into a folder.

I have different sheets I would like to save, so instead coming back to the code and changing the number from 1 to 2 ..etc, I want it to add one to the last number and change the file name to be saved as well then save then and go back add 1 , change file name etc. how can I do that ? 

Sub Macro1()
'
' Macro1 Macro

Windows("FORM 16 - 2018 (FINAL)-test jo.xlsx").Activate
ActiveCell.FormulaR1C1 = "8"
Sheets("Sheet1").Select
Sheets("Sheet1").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
    "\C:\Storedfiles\08-Form 16.xlsx", _
    FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End Sub

Upvotes: 0

Views: 41

Answers (2)

Ferdinando
Ferdinando

Reputation: 964

'how sad @pheeper and
'if you don't know the number of the files that you have to copy and you want to continue to increment
'number for next days you can use two input one to say how many file do you want to copy and from
'which number do you want to star. I hope that this code is helpful for you... 

Dim n1, n2 As Variant   
Dim numberOk As Boolean 

numberOk = False    

'insert the number where do you want to start   
n1 = Application.inputbox("number where do you want to start")  

Do  
    If Not IsNumeric(n1) Then   

        n1 = Application.inputbox("number where do you want to start")  

    Else    

        numberOk = True ' input is a number 
        n1 = CInt(n1) ' convert the number in integer   
    End If  
Loop Until numberOk = True  

numberOk = False    

'insert number where do you want to start   
n2 = Application.inputbox("numbers of copy that you want to do")    

Do  
    If Not IsNumeric(n2) Then   

        n1 = Application.inputbox("number where do you want to start")  

    Else    

        numberOk = True ' input is a number 
        n2 = CInt(n2) ' convert the number in integer   
    End If  
Loop Until numberOk = True  

'change the for in for i= n1 to n2+n1   
'your code...

Upvotes: 0

pheeper
pheeper

Reputation: 1527

I'm just writing this off the top of my head, but you can create a loop from X to Z and then combine the variable number with the file name string. It would look something like this.

dim i as integer    

for i = 1 to 10 
    '
    ' add your code here
    '
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Storedfiles\(" & i & ").xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
next i

Upvotes: 1

Related Questions