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

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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!