CODEWITHSUNDEEP
excelvbaloops
Richard Poole
Richard Poole

Reputation: 13

How to loop macro through a range of cells?

I have a workbook for school grades (I'm a teacher). I have five sheets in the workbook, students enter their username and it will index match all the data and fill in the remaining sheets.

I create a PDF of the workbook. The code copies the username from the Target sheet in cell C2 and pastes it in the cell on sheet one that then populates the whole sheet before exporting to PDF with the username as the filename.

How do I loop the following section so it goes down the list of usernames, effectively C2-C210, exporting the PDF one at a time?

I think it's the code below that needs looping.

'Create and assign variables
Dim Path As String
Dim filename As String
Dim username As String

'.change username value
Sheets("Introduction").Range("B19").Value = Sheets("Target Data").Range("C2").Value

'.create filename
username = Sheets("Target Data").Range("C2").Value
filename = username
Path = "/Users/richard/Desktop/"

'Save active workbook as PDF
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
filename:=Path & filename

Full code.

Sub Looptest()
        
    With Sheet2.PageSetup
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
         
    With Sheet4.PageSetup
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
        
    'Create and assign variables
    Dim Path As String
    Dim filename As String
    Dim username As String
    
    '.change username value
    Sheets("Introduction").Range("B19").Value = Sheets("Target Data").Range("C2").Value
    
    '.create filename
    username = Sheets("Target Data").Range("C2").Value
    filename = username
    Path = "/Users/richard/Desktop/"
    
    'Save active workbook as PDF
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
    filename:=Path & filename
    
End Sub

Upvotes: 1

Views: 72

Answers (1)

Storax
Storax

Reputation: 12167

My Suggestion would be to do it like that

Sub LoopIt()

Dim rgUsernames As Range
Set rgUsernames = Sheets("Target Data").Range("C2:C210")

Dim sngUsername As Range
For Each sngUsername In rgUsernames
    Looptest sngUsername.Value2
Next sngUsername

End Sub

The above code will loop through the usernames which according to your post are in Range("C2:C210") on sheet Target Data. Looptest will then export to a PDF file for each single username.

Sub Looptest(UserName As String)
    
    
With Sheet2.PageSetup
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
     
With Sheet4.PageSetup
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
End With
    
'Create and assign variables
Dim Path As String
Dim filename As String

'.change username value
' Sheets("Introduction").Range("B19").Value = Sheets("Target Data").Range("C2").Value
Sheets("Introduction").Range("B19").Value = UserName

'.create filename
' username = Sheets("Target Data").Range("C2").Value

filename = UserName
Path = "/Users/richard/Desktop/"

'Save active workbook as PDF
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
filename:=Path & filename

End Sub

Upvotes: 1

Related Questions