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") Sheets("Paste").Select Range("A2:AN2").Select Selection.Copy Sheets("Template").Select Range("A2").Select ActiveSheet.Paste 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!!