VBA Macro To Convert Sheet To Values Only & Rename

  • In a workbook I have a master form (sheet 1) and a summary log (sheet 2) and behind these I am saving copies of the unique completed forms per event / incident.
    1. User enters data on sheet 1 (master form).
    2. Click macro button to copy select data to sheet 2 (summary log). Code not shown.
    2.a Copy completed sheet 1 with unique # in L1:M1 (e.g."0213-100") to back of workbook via paste special to only copy text and formatting (1 form snapshot per event / incident).
    2.b Rename copied sheet/tab (now sheet 3+) with the unique # in L1:M1 (e.g. "0213-100").
    2.c Clear master form and assign next sequential # (per # list in log), ready for next use.
    2.d Save changes.


    Need some help please with:
    Step 2.a - to make the paste special work for text and formatting only
    Step 2.b - the sheet/tab renaming (e.g. "0213-100") as it is currently retaining the name of the master form.


  • Re: VBA Macro To Convert Sheet To Values Only & Rename


    Dave, thx for your response, but I think my post was not clear or perhaps incomplete - my apologies. I did some research on "UsedRange" and it is certainly more efficient but still not working for me.
    Need some help please with:
    Step 2.a - to make the paste special work for text and formatting only
    Update: This relates to copying sheet 1 (master form, Range A1:N58) to the back of the workbook but without formulas or data validation (values only?), if possible.
    Step 2.b - the sheet/tab renaming (e.g. "0213-100") as it is currently retaining the name of the master form.
    Update: This relates to renaming the above saved sheet with the text in L1 and M1 combined (e.g. "0213-100"). For each sheet logged and saved to the back, the number (sheet name) will increase by +1, so it will be unique with each sheet saved.

Participate now!

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