Copy select range to a new sheet, and label it based on form field.

  • I'm using a project to help me learn VBA. I was searching Google for answers and came upon this forum... fantastic! This is my first post.

    The topic I'm focusing on today is copying a range from the current, active sheet to a new sheet, with a date entered on a user-form as the sheet's name. Hopefully the attached image will explain more. It's a little more complicated than some of my other "lessons," so I thought I'd try to get some help.

    The project is a yearly preventative maintenance worksheet. The equipment specifics remain the same from year to year, but the measured data will be different. So after entering in the date of the PM (ie, 2020-03-15) a the current, active sheet (specific column range, ie. A thru J) is copied to a new sheet, which is given the date-name entered in the user form text field. All of my Command buttons on the top of the page would need to be copied as well, since they activate forms to edit each record ("get" and "let").

    Any thoughts on how to accomplish this?

  • Here's a little helper function that should sort you out :)

  • Once you have created your new sheet you are free to just copy data to it as you see fit. For example:

  • It's hard to work with a picture so it would be easier to suggest a specific solution if you could attach a copy of your file (de-sensitized if necessary).

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Here's a little helper function that should sort you out :)

    Thanks. I've been trying to make this work, but some of this is beyond my skill level, and I can't figure out how to troubleshoot it. I'll dig around and find examples to build upon. It's good to know this forum is here when I have specific questions. Thanks again.


  • I figured out how to accomplish what I needed. InputBox was the key. I found that the new sheet was better to be entered in front of all the other sheets. This worked perfectly!

  • Great stuff. Always more satisfying when you solve it yourself 😀

    Just a word of warning. What happens if you accidentally enter a name that would be a duplicate of an existing sheet? What happens if you press cancel?