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


    Maggie

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


    Thanks.

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

    linecopy.xlsb


    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.