Macro help

[doobas]

Hi all. I'm making a costing model for work, and need to have a function that will copy a sheet from a locked workbook into a new workbook, whilst keeping the sheet name of the original.

I've managed to do this for a single named sheet. BUT, the locked workbook has 15 sheets so far, and will be expanded over time.

I know I can just copy the macro for each sheet, changing the name of the sheet copied, but due to the size of the project, a 'One size fits all' macro that can do this for me would be of great help.

Thanks in advance for any help given.

doobas™

 

[AlphaFrog]

This copies all worksheets from the workbook "Book2.xls" to the workbook that has this macro in it.

Sub Copy_All_Sheets()
    
    Dim wbSource As Workbook
    Dim wbDestination As Workbook
    
    Set wbSource = Workbooks("Book2.xls")
    Set wbDestination = ThisWorkbook
    
    wbSource.Sheets.Copy After:=wbDestination.Sheets(wbDestination.Sheets.Count)

End Sub

 

[doobas]

Just to clarify, I just need to know if there is a 'copy current sheet' function.

doobas™

 

[AlphaFrog]

Activesheet.Copy

 

[doobas]

Activesheet.Copy

This is exactly what I was looking for. Thank you

doobas™

 

[doobas]

On a related note, is it possible to name the new workbook that is generated from the macro I have running? At the moment, it will name the worksheet that is copied, but will use the default 'Bookx' naming for the file.

doobas™

 

[AlphaFrog]

The workbook is renamed when you save it.

 

[doobas]

The workbook is renamed when you save it.

You mean, when the user goes to save it will open the save dialogue box and then they can input the name they require?
I only ask, because that's what is happening now. The name needs to be manually put in, but I'd prefer it to auto-populate the name, so that there can be little confusion.

You don't know the ineptitude levels of the people who will be using this file

doobas™

 

[AlphaFrog]

Do you want to prompt the user with a Save As dialog that has an auto-populated name?

Or

Do you want to just have the macro save the workbook with the name and not prompt the user?


How do you determine (specifics) the workbook name?

Where (folder) do you want to save it?

 

[doobas]

I want it to auto populate the name with what the active sheet is called (maybe add in the date?), and if it could default to save in the My documents in the C folder would be great.

This program will be on a server, completely locked off barring a few input boxes. It will be accessed by people up and down the country, and most of them are IT Illiterate. So I want it to automate as much as it can to minimise errors.
That's why I needed a macro to copy the sheet they are using to a completely different file.

Thanks for your help, mate. It's much appreciated.

doobas™