vba to refer to a sheet name based on a cell's content

[cjcass]

Hi,
I'm looking for some vba that will enable me to refer to a wksheet name based on the contents of a cell.
My existing code is as follows:

Sheet15.Select
Range("A7:CU10000").Select
Selection.Copy


Sheet19.Select


Range("A7").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A7").Select

I'm looking to replace the code 'Sheet19.Select' in the middle with the name of a wksheet that will be typed into cell B4. So, as an example, if I want the data to be pasted into a wksheet named '23' (the tab name), I will type the number '23' into B4 and then run the macro.
Hope this makes sense and you can help, many thanks.

 

[cjcass]

Hi,
Cell B4 will have a number input into it, eg. 23, and there will be a sheet with that same number as it's tab name. So, if I was recording a macro and selected that sheet the code would read Sheets("23").Select

 

[JLGWhiz]

Hi,
Cell B4 will have a number input into it, eg. 23, and there will be a sheet with that same number as it's tab name. So, if I was recording a macro and selected that sheet the code would read Sheets("23").Select

Ok, that is how I interpreted your original post and that is the assumption used for the code in Post # 7. The 'sh' variable would equate to 'Sheets("23")' if you put "23" in cell B4. Anywhere the 'sh' appears is the same as Sheets("23"). I eliminated the Select statements and used direct coding so that the flicker and flash of the screen updating would go away. The code in post #7 should work as you wanted.

 

[cjcass]

It's still using the sheet's code name as opposed to it's string name (tab name) and this is your assumption in #7

 

[JLGWhiz]

It's still using the sheet's code name as opposed to it's string name (tab name) and this is your assumption in #7

I am not following you. This

Set sh = Sheets(Sheets("Data").Range("B4").Value)

Should yield a value of 'Sheets("23")' if B4 contains '23'. You can verify that value with a message box place immediately after the variable is initialized.

MsgBox "Sheets(" & sh.Name & ")"

If it Shows 'Sheets("23")' it should be correct, but if it shows 'Sheets(Sheet23)' Then we have a communication problem.

 

[cjcass]

It's definitely a communication problem.
Your message box returns.... Sheets(23)
I'm having to type 21 (the sheet code name) into cell B4 in order for the data to be copied to 23 (the visible sheet/tab name in the wksheet).
If you were to see my VBA window you'd see the sheet is detailed as follows... Sheet21 (23)
I need to be able to type 23 in B4 and the data to go to 23 (tab name)

 

[JLGWhiz]

It appears that the value in B4 is Numeric rather than string, so this should fix it if you modify the code as follows.

Set sh = Sheets([COLOR=#ff0000]CStr([/COLOR]Sheets("Data").Range("B4").Value)[COLOR=#ff0000])[/COLOR]

Just for clarification, it was not using the code name, it was using the sheet index (23rd sheet left to right as they appear on the sheet name tabs.) That was what had me confused, because I didn't know how it could reconfigure the code structure to use the Excel code name.

 

[cjcass]

Yeehaa!! that works...
Brilliant, thanks very much for your time and perseverance with this

 

[JLGWhiz]

Yeehaa!! that works...
Brilliant, thanks very much for your time and perseverance with this

You're welcome,
regards, JLG