sheet name as variable

[oercim]

Hello. In excel macro, I want to make below statements:

1. adding a sheet whose name is a variable( name is taken
from the the cell:sheet1,A1)
2. pasting some rows from another sheet to added sheet.
I made the below code, but it doesn't work

Sub d()
Dim sht As Worksheet

Set sht = Sheets("sheet1").Range("A1").Value

Sheets("sheet2").Rows("10:20").Copy.Sheets(sht).Range("A10")
End Sub

Thanks a lot.

 

[oercim]

It gives error at the

"Set sht = Sheets("sheet1").Range("A1").Value"

line but I couldn't manage. thanks.

 

[Norman Jones]

Hi Oercim,

Try this adaptation:

'=============>>
Sub d()
Dim sht As Worksheet

Set sht = Worksheets.Add
sht.Name = Sheets("sheet1").Range("A1").Value

Sheets("sheet2").Rows("10:20").Copy Destination:=sht.Range("A10")
End Sub
'<<=============

 

[oercim]

Hello Norman, It gives error in line

"sht.Name = Sheets("sheet1").Range("A1").Value"

Thanks alot.

 

[oercim]

Hello Norman, It gives error in line

"sht.Name = Sheets("sheet1").Range("A1").Value"

Thanks alot.

 

[JE McGimpsey]

What error is it giving?

Is Sheet1 the name of the reference sheet?

Is the value in A1 a valid name?

Do you already have a sheet with the name in A1?

 

[oercim]

Sorry, it worked. Thank u very much. Cheers