Create a new sheet for every Date or paste it below the existing date if the sheet exists

[SPACE8ARD]

Hi There,

I want to create a new worksheet for every date or if the sheet exists paste the data in that sheet below the existing data.

I am new to this so I need help. I have also created two buttons to test the code

I found these codes from Mr Ron De Bruin's website and modified it to create new sheets with date as sheet name but when I try the paste the data in the existing sheets it is not working and new sheets are created without any name.

Hope you guys can help

[patel45]

Can you attach a sample file with data and desired result ?

[AB33]

The code you have works. See attached.

[SPACE8ARD]

Hi AB33,

Thank you for your reply

The code works the first time to create new worksheets but when I try to paste data (the second time) in the existing worksheets it does not work. You can check this out by clicking Button 1 the second time.

Hope you can help me with this.

[SPACE8ARD]

Hi There,

I have attached the file, the file contains two command buttons and when the first button is clicked new worksheets with dates as names are created but the problem occurs when I try to paste the data the second time into the existing sheets.

Hope you can help

[JOHN H. DAVIS]

Are you missing an attachment in Post#5?

[SPACE8ARD]

Hi John,

The file was attached to the first post and also to the 3rd

[AB33]

Look at the attached. I re-run my code to check it copies in to next row. Module 4.

[SPACE8ARD]

Hi AB33,
Thank you very much for your help,
Sorry I could not get back sooner.
Your code works fine when there is an existing worksheet with date as its name.
However when I tried to append the next set of data this gave an error. (Run time error 91- Object variable with block variable not set) in the line
NR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1
I do not know the reason can you please help?

[AB33]

You can use this line, but I have amended the code to copy the headers for new sheets.

Please Login or Register  to view this content.

[SPACE8ARD]

Hi AB33,

Thank you very much, I found out that your previous code needs a Header to work properly, Once I named a sheet and gave a header it works beautifully.

The new code however copies everything twice!! So I need your help once again, sorry for the bother.

[SPACE8ARD]

Hi AB33,

I used the line of code as you suggested and it works - creates new worksheets and copies the data as well. Fantastic!!! THANK you very much