VBA Loop - copy range in row, paste on another worksheet, save as one drive path with cell as name, do the same for the next row until blank

  • 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 ;(

    Thanks in advance!!


  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


  • Hi,

    You can try this code:

    I have it working in this workbook, you need to create a directory on your C drive called "c:\test" just press the button on the template sheet:


    I do not know how the onedrive directory works, but if this code is doing some of what you want I can have a look working out the directory.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!