Hi VBA Magicians!
This is my first post because I'm pulling my hair out trying to get this to work. I would GREATLY appreciate any help.
Here's what I'm trying to do:
1. Copy range A2:AN2 on Sheet named "Paste"
2. Paste on another worksheet named "Template"
3. Save as one drive path (multiple users so the User name has to be dynamic)
"C:\Users\" & Environ$("Username") & "\OneDrive - COMPANY\Documents - Location\"
4. Save Filename as cell AO2 on "Template" sheet
5. Go back and repeat process but this time using row 3 on Sheet named "Paste"
The following is what I have so far (copied and pasted from various other posts) but it stops at the first A2:AN2 and doesn't move onto A3:AN3 after saved
- Dim src As Workbook
- Dim SaveName As String
- Dim wb As Workbook
- Dim dst As Workbook
- Set src = ActiveWorkbook
- For Each c In Range("A2:A100")
- i = c.Row
- SaveName = Sheets("Template").Range("AO" & i).Text
- ActiveWorkbook.SaveAs Filename:="C:\Users\" & Environ$("Username") & "\OneDrive - COMPANY\Documents - Location\"s\" & _
- SaveName & ".xlsm"
- i = i + 1
- Next c
- End Sub
Thanks in advance!!