Copying data from 1 sheet to another in vba

[Elddis]

I am new to VBA I have a workbook with multiple worksheets 50+ I want to open a sheet and copy data from that sheet to another sheet, the code I have tried is:

Sub Copy_inventory()
Call Clear_Price_List
lastrow = Sheet11.Cells(Rows.count, 2).End(xlUp).Row

Dim rgSource As Range, rgDestination As Range

Set rgSource = Sheet11.Range("B2:E" & lastrow)

Set rgDestination = Sheet4.Range("C12")

rgSource.Copy
rgDestination.PasteSpecial xlPasteValues

End Sub

This works but i want change the sheet i have a cell on sheet 4 that has a lookup on it, when item is selected a vlookup returns the sheet coded name (Sheetx not the Caption)
I have tried replacing the Sheets with the cell value but doesnt work.
thank you in advance

 

[Kaper]

Instead of using Sheet11.Range(.... construction which relates to sheet object codename use names of sheets.

In newly created workbook these are just Sheet1, Sheet2, ... so for the eleventh sheet you can instead of Sheet11.Range( use

Sheets("Sheet11").Range(...

And once you give own names to sheets like Sales, Staff, etc. refer to them as

Sheets("Staff").Range(...
Sheets("Sales").Range(...

and so on.

 

[KOKOSEK]

It is out of issue in thread but I would like to ask why You (@Kaper) recommend referring into sheets by 'name' not a codename?
If anyone for any reason want to change its name, there are could be a lot to change in VBA code. If You use codename, use can change its name as he wants to.

 

[Kaper]

@KOKOSEK - There (I think) is no one universal solution. This is probably why there is a name and codename.

Honestly - description by Elddis was not perfectly clear to me, and still isn't.

But to focus on codename and name: to refer to a sheet with variable name refering is pretty easy while for a reference to variable codename we shall (or may be there is an easier way?) use a bit more complicated construction like:

Dim sh as Worksheet, cn as String
' Cycle through all worksheets until desired CodeName is found
For Each sh in ThisWorkbook.Worksheets        
' Let's assume that the codename mentioned in post #1: "i have a cell on sheet 4 that has a lookup on it"
' is in A1 in Sheet11
  cn =  Sheet11.Range("A1").Value
    If sh.CodeName = cn Then            
' variable sh refers to desired sheet
' and can be used in a way like:
     Set rgDestination = sh.Range("C12")
' or similar
    End If
Next sh

And what is your proposition to solve problem from post #1?

 

[Elddis]

I want to use code name such as sheet1 etc rather than caption name for the exact reason Kokosec put regarding renaming, as users are unlikely to change codenames but can easily change the caption names, not only that but I have list of codenames and sheet names on the first sheet where the user can change the caption names of sheets easily, but not the code name.
I use the sheet name list in my drop down list when an item is selected then a vlookup returns the sheet code name and displays it I am trying use this vlookpup cell in the vba.

 

[Kaper]

So you probably can use the code snippet I gave in post #4

 

[Elddis]

Thank you Works perfectly much appreciated

 

[Kaper]

Glad to hear so