Saving input into 2nd worksheet while keeping the 1st empty

[Closed]
Report
Posts
1
Registration date
Sunday July 23, 2017
Status
Member
Last seen
July 23, 2017
-
Posts
2811
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 27, 2021
-
Hi there,

Spoiler alert: Excel dummy

I hope you can help me with this. I need to have a worksheet that would be used on daily basis and can be archived. It should be able to do the following:

1. I open worksheet "Checklist" which brings me to the following template:

Task (A1) Comment (B1) [Archive]
Task 1 (A2) _________ (B2)
Task 2 (A3) _________ (B3)
Task 3 (A4) _________ (B4)

3. I put all the comments and press Save. Then I click on [Archive] button which saves the information on specific location with name - Checklist[current date], while keeping me in the main worksheet which is now empty.

As the Checklist should be used everyday, the main file should always have empty comment fields so every input needs to be erased after it is archived to another sheet.

Basically, if it is possible [Archive] Macro to do:

1. Open second worksheet.
2. Copy Sheet1 from the main (working) worksheet (where I input comments).
3. Paste the copied into the second worksheet.
3. Save As the second worksheet under the name "Checklist[current date] on specific location
4. Close second worksheet.
5. Erase the input (comments) from the main worksheet.
6. Save.

Thanks in advance!

1 reply

Posts
2811
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 27, 2021
484
Hi Britany,

Important note:The code below both deletes data and saves your workbook. So make sure you test this with a copy of your workbook !!!

Here is the code:
Sub RunMe()
Sheets("Checklist").Cells.Copy
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Checklist " & Date
Range("A1").PasteSpecial
Sheets("Checklist").Select
Range("B2:B" & Range("B1").End(xlDown).Row).ClearContents
ActiveWorkbook.Save
End Sub

Since you gave me a spoiler, let me provide some extra info on using codes:

How to implement and run a code:

- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.

Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month