VBA to create new worksheet with new name, contents and formatting

[Samlise]

Hi All

I am very new to VBA so would like to ask you all for your expert advice

I have a file with two worksheets, one worksheet (Log) is a list of all the user inputs
The other worksheet (Template) is formatted how I need it to be copied over for the extra worksheets

So...

In the 'Log' worksheet, cell N1, I have a formula that finds the last populated cell and I need to use this name as the worksheet name when created by the macro, so every time a report is inputted I get a new name for the new worksheet.

The other worksheet (Template) needs copying over exactly how it is, renaming as above from N1 then certain data from the 'Log' sheet needs copying over to the newly created sheet, O2 to H3, P2 to X3, Q2 to H4.

Any help would be appreciated, I have been searching google all morning for inspiration but with no joy

Cheers

Carlos

 

[dnorm]

Hi Samlise

If you can, you could record a macro for most of what you are asking(Selecting the cells to copy).

To copy and rename your template sheet add this in your VBA

Sub New_Sheet

Sheets("Template").Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = Sheets("Log").Range("N1").Value

End Sub

I am not sure how to get rid of the popup claiming the sheet name exists (until the next line changes it) but it will add and rename.

 

[Samlise]

Brilliant thanks dnorm

That works a treat, I've recorded the copy section but how do I change the specific Sheets("sheet1").Select to be able to paste it to the latest created sheet

Cheers

 

[Samlise]

I've figured it out...

I'm copying to the template, copying the template to the new workbook with a new name then going back and clearing the template.

Cheers for your help dnorm

 

[dnorm]

in the VBA order it so:

create the new sheet first,​

select the data you want to copy​

paste data into your new sheet​

 

[dnorm]

Glad I could help.

Next time, try recording what you want to do, look at the macro and then look to see what others are asking for, that's how I am learning.