VBA Function Loop Through Range

[cpadilla0024]

Have been stuck on getting this one to work. I am trying to loop each cell in a range through the VBA process and having trouble setting up the loop. If it helps, I have named the range "generatedivfiles" (AR2:AR25) and the "AR2" below is the first cell in the range. I thought naming it would make it more dynamic in case we expand to more divisions. Code is below (works the way I want besides the loop):

Private Sub gendivfiles_Click()


'Copy division names over to BPW
Worksheets("controls").Range("AR2").Copy
Sheets("Non Labor BPW").Range("C3").PasteSpecial Paste:=xlPasteValues


'Protect workbook
Dim ws As Worksheet
Dim pwd As String

pwd = "bpadreamteam" ' Put your password here
For Each ws In Worksheets
ws.Protect Password:=pwd
Next ws

'Make appropriate sheets very hidden
Sheets("controls").Visible = xlVeryHidden
Sheets("query").Visible = xlVeryHidden
Sheets("Data Load").Visible = xlVeryHidden
Sheets("data").Visible = xlVeryHidden
Sheets("controls").Visible = xlVeryHidden
Sheets("BPA Analyst Steps").Visible = xlVeryHidden
Sheets("unique keys").Visible = xlVeryHidden

'Save copy of workbook
Application.DisplayAlerts = False
cell = Range("A27").Value
Fpath = "S:\Finance\BUDGET\UCDMC\2223\BPWs\Non-Labor BPW for Divisions\"
Fname = Fpath & cell & ".xlsm"
ActiveWorkbook.SaveCopyAs FileName:=Fname

'Unhide all sheets
For Each ws In Worksheets
ws.Visible = True
Next

'Unprotect all sheets
For Each ws In Worksheets
ws.Unprotect Password:=pwd
Next ws


End Sub

 

[Max1616]

Perhaps try something like this? Note - I added some code to paste the results in the C3, C3+1 ,...C3+N...

Dim rng As Range
Dim c As Range
Dim counter As Integer

Set rng = Worksheets("controls").Range("AR2:AR25")

counter = 0

For Each c In rng.Cells


c.Copy
Sheets("Non Labor BPW").Range("C" & counter + 3).PasteSpecial Paste:=xlPasteValues

counter = counter + 1

Next c

You could also potentially do this without a loop:

Worksheets("controls").Range("AR2:AR25").Copy
Sheets("Non Labor BPW").Range("C3").PasteSpecial Paste:=xlPasteValues

 

[cpadilla0024]

Thanks Max. I do need to do one-by-one unfortunately because that paste is going into a form that will give users permissions accordingly. I tried the below and got an error with the Bold Red area below. Everything I had in my original macro is the entire process I need. I just need it to loop through AR2:AR25. Copying each one to Non Labor BPW and then running through the process. Then A3, process......

Private Sub gendivfiles_Click()

Dim rng As Range
Dim c As Range
Dim counter As Integer

Set rng = Range("AR2:AR25")

counter = 0

For Each c In rng.Cells


Worksheets("controls").Range("AR" & c.Row).Copy
Sheets("Non Labor BPW").Range("C" & counter + 3).PasteSpecial Paste:=xlPasteValues

counter = counter + 1

Next c


'Protect workbook
Dim ws As Worksheet
Dim pwd As String

pwd = "bpadreamteam" ' Put your password here
For Each ws In Worksheets
ws.Protect Password:=pwd
Next ws

'Make appropriate sheets very hidden
Sheets("controls").Visible = xlVeryHidden
Sheets("query").Visible = xlVeryHidden
Sheets("Data Load").Visible = xlVeryHidden
Sheets("data").Visible = xlVeryHidden
Sheets("controls").Visible = xlVeryHidden
Sheets("BPA Analyst Steps").Visible = xlVeryHidden
Sheets("unique keys").Visible = xlVeryHidden

'Save copy of workbook
Application.DisplayAlerts = False
cell = Range("A27").Value
Fpath = "S:\Finance\BUDGET\UCDMC\2223\BPWs\Non-Labor BPW for Divisions\"
Fname = Fpath & cell & ".xlsm"
ActiveWorkbook.SaveCopyAs FileName:=Fname

'Unhide all sheets
For Each ws In Worksheets
ws.Visible = True
Next

'Unprotect all sheets
For Each ws In Worksheets
ws.Unprotect Password:=pwd
Next ws


End Sub

 

[cpadilla0024]

Actually it is solved. Thanks so much for your help! I tinkered the arrangement a little and it worked perfectly.