Pasting clipboard in a macro 1

[cameronem20]

Hello,

I realise this is a very basic query as I am not used to using VBA. What I am wanting to do is paste the contents of cell A1 on worksheet Control to rename other worksheets. What I am finding is that when using the wizard to record the macro it is using the value used in the cell when the macro was recorded rather than whatever is in the cell.

Also is it possible to use these contents to rename the first part of a worksheet name (ie. I am copying a worksheet and renaming the first part of it eg CSR summary to clipboard contents summary))

The following is the code there at the moment.
Sub NewCSR()
'
' NewCSR Macro
' Macro recorded 20/03/2004 by Administrator
'

'
Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(5)
Sheets("CSR summary").Select
Sheets("CSR summary").Copy After:=Sheets(6)
Sheets("Control").Select
Range("A1").Select
Selection.Copy
Sheets("Template (2)").Select
Application.CutCopyMode = False
Sheets("Template (2)").Name = "Joe"
Range("E5").Select
Sheets("Control").Select
Range("A1").Select
Selection.Copy
Sheets("CSR summary (2)").Select
Application.CutCopyMode = False
Sheets("CSR summary (2)").Name = "Joe summary"
Range("A22").Select
Sheets("Control").Select
End Sub



Thanks heaps

 

[PHV]

Something like this ?
Sheets("Template (2)").Name = Sheets("Control").Range("A1").Value
Sheets("CSR summary (2)").Name = Sheets("Control").Range("A1").Value & " summary"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[cameronem20]

Excellent, thanks so much for that it works perfectly!

I am sorry for asking all these questions - this project has been dumped on me to complete and I have very little (next to no knowledge) with VBA

Now for another question extending on my initial one...

How to rename on the sheets("Control").Range("A1").Value & " summary" sheet (ie the newly created CSR summary sheet) and find and replace the "Template" with
the value in the Sheets("Control").Range("A1").Value

 

[cameronem20]

One more thing with the find and replace - how can I enable this to do so within the formulae that are in the cells..