help needed for copying onto arbitrary named wksheet

[dimash]

Evenin' lads n lasses

My boss has asked me to knock up a few spreadsheets and Im way out of touch with VBA, and Excel for that matter. I have tried my best to read up but the boss is wanting them pretty soon. Was wondering if anyone could possibly give any pointers.

Im assuming help similar to this must get asked a lot, so apologies.

The first worksheet is straightforward, input values in one column, output values in the second. Sorted. What im after doing is writing a macro that will open a template in a new worksheet (in which i can provide a name via a msgbox). The template will contain a table and hence the pertinent output values from first wksheet.

Now, i can produce a new worksheet and give a name of my choice via the code:

Dim result As String
ActiveWorkbook.Worksheets.Add After:=Worksheets ...(Worksheets.Count)
result = InputBox("Input Ref Name")
If result <> "" Then
ActiveSheet.Name = result
End If

Problem 1 is im unsure how to create a template.
Problem 2 is im having problems with copying data values.

By recording a macro i can easily copy selected ranges across. What i cant seem to fathom (no laughing please!) is how to write a macro that will recognise the arbitrarily named worksheet that is to be created. for instance, i can copy across to a specifically named worksheet, or an inserted 'new' wksheet, but im wanting to link the two together so that one macro can create a new worksheet, which i can name as anything, and then have data automatically copied across.

Eg. code for the copying is

Range("A23:I27").Select
Selection.Copy
Sheets("Data").Select
Sheets.Add
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C15").Select

As seen this 'adds' a sheet, doesnt direct it to the newly (arbitrary) named wksheet.

Hope this is clear enough.

thanks for ya time and any help greatly appreciated.

cheers

D

[Bob Phillips]

[vba]

Dim sh As Worksheet
Dim rng As Range

With ActiveSheet

Set sh = Sheets.Add
Set rng = .Range("A23:I27")
rng.Copy sh.Range("A1")
sh.Range("A1").Resize(rng.Rows.Count, rng.Columns.Count).Value = _
sh.Range("A1").Resize(rng.Rows.Count, rng.Columns.Count).Value
End With
[/vba]

[dimash]

cheers man, appreciate it. Will post again when i test it.