Solved: Read Sheet name from a Cell

[afzalw]

I want to change sheet number 5 to a name of sheet that is written in cell B6 of Sheet(1).
e.g Cell B6 in Sheet(1) has "Data1" written in it.
Thanks

[vba]
Set rDest = Sheets(5).Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
[/vba]

[GarysStudent]

Perhaps:

[VBA]Sub dural()
Sheets(5).Name = Sheets(1).Range("B6").Value
End Sub
[/VBA]

[afzalw]

I actually don't want to change the name, instead the name of sheet(5) is already "Data1" but I want my code to read the name of sheet from cell B6 of Sheet(1). e.g B6 already have the name of sheet "Data1"

[sassora]

Do you mean the reverse?

Sub dural()
Sheets(1).Range("B6").Value = Sheets(5).Name
End Sub

[afzalw]

So this was what I originally posted

[vba]

Set rDest = Sheets(5).Range("B" & Rows.Count).End(xlUp).Offset(1, 0)

[/vba]
Here the sheet number is 5 but I don't want to it to call this sheet by its number instead I want it to call it by its name which is "Data1".

I can replace 5 with "data1" here right!!! and it will work...... but sheet name is not fixed every-time so I want it to call this sheet name from cell A1 of sheet1.

So that every time sheet name written in cell A1 of sheet1 is changed then sheet name here will also change.

This should work, but not working !
[VBA]Sheetname = ThisWorkbook.Sheets(1).Range("A1")

Set rDest = Sheets("Sheetname").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
[/VBA]


Thanks

[SamT]

[vba]Set rDest = Sheets(Sheets("Sheet1").Range("A1")).Range("B" & Rows.Count.End(xlUp).Offset(1, 0)) [/vba]

[afzalw]

[vba]Set rDest = Sheets(Sheets("Sheet1").Range("A1")).Range("B" & Rows.Count.End(xlUp).Offset(1, 0)) [/vba]

Did not worked I already tried.

Its same as
[VBA]sheetname = Sheets("Sheet1").Range("A1")
Set rDest = Sheets("sheetname").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)[/VBA]

Sheetname has the name Data1 stored in it but does not work here don't know why!

[GarysStudent]

Drop the double quotes:
[VBA]
sheetname = Sheets("Sheet1").Range("A1")
Set rDest = Sheets(sheetname).Range("B" & Rows.Count).End(xlUp).Offset(1, 0)[/VBA]

[afzalw]

Drop the double quotes:
[vba]
sheetname = Sheets("Sheet1").Range("A1")
Set rDest = Sheets(sheetname).Range("B" & Rows.Count).End(xlUp).Offset(1, 0)[/vba]

Ok I did try that before it only works when your sheet name is alphanumeric sometimes my sheet names are only numeric..
e.g
its working for Data1 but If my sheet name is 123 then it wont work

[SamT]

sometimes my sheet names are only numeric..

That's nice to know.

[VBA]sheetname = Sheets("Sheet1").Range("A1").Text [/VBA]

[afzalw]

That's nice to know.

[vba]sheetname = Sheets("Sheet1").Range("A1").Text [/vba]

It didn't worked before and its working now .

Thanks