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.

 

[JLGWhiz]

this declares 'sh' as a worksheet object, then sets the variable value as a worksheet named for the value in sheet19, cell B4.

Sub t()
Dim sh As Worksheet
Set sh = Sheets(Sheets("Sheet19").Range("B4").Value)
With sh
    MsgBox .Name
End With
End Sub

 

[cjcass]

ok, can you show me where this fits into my existing code please, thanks.

 

[JLGWhiz]

ok, can you show me where this fits into my existing code please, thanks.

You tell me which sheet you intend to use cell B4 to enter the sheet name into.

 

[cjcass]

The sheet is named 'Data'

 

[JLGWhiz]

Dim sh As Worksheet
Set sh = Sheets(Sheets("Data").Range("B4").Value)
 Sheet15.Range("A7:CU10000").Copy
 sh.Range("A7"),Select

It would be more efficient if you learned to write the code without using Seclect and Activate.

 

[JLGWhiz]

If you want to paste the copied data into that sheet at cell A7 then

Dim sh As Worksheet
Set sh = Sheets(Sheets("Data").Range("B4").Value)
 Sheet15.Range("A7:CU10000").Copy sh.Range("A7") 'assumes code name for sheet is used

If you have formulas and only want to paste values into the sheet then

Dim sh As Worksheet
Set sh = Sheets(Sheets("Data").Range("B4").Value)
 Sheet15.Range("A7:CU10000").Copy 'assumes code name for sheet is used
 sh.Range("A7"),PasteSpecial xlPasteValues

Notice there are no Activate or Select statements used. Also, if you ran the code as a macro, there would be no flicker and flash.

 

[cjcass]

Hi,
Thanks for your work on this, almost there...
I need the code 'sh.Range("A7")' to refer to the worksheet's tab name as opposed to it's code name if possible, thanks.

 

[cjcass]

any help would be much appreciated

 

[JLGWhiz]

Hi,
Thanks for your work on this, almost there...
I need the code 'sh.Range("A7")' to refer to the worksheet's tab name as opposed to it's code name if possible, thanks.

Show me what you put in cell B4. I understood that you used numbers for names. In VBA sheets can be referred to as Sheets(1), using the collection index number, or Sheet1, which is the code name, or Sheets("Sheet1"), which uses a string name. Believe it or not, these three references can be three entirely different sheets in the same workbook. The code I used for the 'sh' variable assumed that you were using a string name in cell B4. If you are using the index number then the code would be slightly different. If you have the full reference, eg. Sheet19 or Sheets("19"). then I need to know exactly what is in that cell B4.