Input Box & Copy together.

[paulsolar]

Hi All

I'm having a practice at input boxes before tackling a larger project later (never used one before).

Anyway I came up with the little bit of code below which has given rise to another question. The code works fine creates a new worksheet with the correct name from the input box and copies the original sheet to the work sheet. I've never used this copy method before but it looks a very simple and quick bit of code to use.

My question is what commands can you use when you send the original to the destination to keep formatting the same? And how do you insert the commands?

I've had a look in my books and on line but cant seem to find this information anywhere.

Is there a reference site to look at anywhere?

Kind regards

Paul

Sub Butt*******()


Dim a, b
Sheets.Add After:=Sheets(Sheets.Count)
a = Sheets.Count
b = InputBox("Please enter the name you wish to use for this Complaint. A seperate worksheet will be generated for each New Complaint")
Sheets(a).Name = b


Worksheets("Enter New Complaint").Range("A1:D34").Copy _
       Destination:=Sheets(b).Range("A1:D34")
       
       


End Sub

 

[dmt32]

Hi,
you can use pastespecial to specify what you want to include when copying & pasting something like this:

Worksheets("Enter New Complaint").Range("A1:D34").Copy

    With Worksheets(b).Range("A1")
        .PasteSpecial Paste:=xlPasteValues
        .PasteSpecial Paste:=xlPasteFormats
        .PasteSpecial Paste:=xlPasteColumnWidths
    End With

You can find more about pastespecial in VBA helpfile & online.

Alternatively, if you have a template sheet in your workbook, you could just copy that & rename it.

Hope helpful

Dave

 

[paulsolar]

Hi Dave

thanks very much for your reply, I have used the paste special functions quite a lot in recent times but I wasn't sure if it was possible to use it with the Destination:=Sheets(b).Range("A1:D34") method of copy paste

Many thanks

Paul