sheet name as variable
 

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).Ra nge("A10")
End Sub

Thanks a lot.

sheet name as variable
 

It gives error at the

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

line but I couldn't manage. thanks.

sheet name as variable
 

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
'<<=============


---
Regards,
Norman


"oercim" wrote in message
ups.com...
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).Ra nge("A10")
End Sub

Thanks a lot.

sheet name as variable
 

Hello Norman, It gives error in line

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

Thanks alot.

sheet name as variable
 

Hello Norman, It gives error in line

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

Thanks alot.

sheet name as variable
 

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?

In article . com,
"oercim" wrote:

Hello Norman, It gives error in line

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

sheet name as variable
 

Sorry, it worked. Thank u very much. Cheers