Excel 2010 - Sharing one common Defined data name range by multiple sheets

**chamdan** · 2013-12-07, 01:05 AM

Hi,

Is there a way to share a defined data name range within the same workbook by most of the worksheets?

I have approximately 25 worksheets within one workbook that contains the same format but different type of data. As a header let us say cell $E4 is defined as CCGroup (Cost Center Group)

and in Cell $E3 the corresponding Cost center name will be displayed automatically based on the Cost center Group code.

All the sheets have exactly the same header but the value found in the cost center is different.

You help would be much appreciated.

Regards,

Chuck

**chamdan** · 2013-12-07, 06:16 AM

I finally decided to take another approach, which does not complicate my task and have used the simple way of selecting the range in each worksheet and fill in the the worksheet name and search for the code inserted at the range using a Vlookup technique. So finally made to work the way I wanted.

HTML Code:

Sub CreateMultipleWorksheets()
Dim r As Range
Dim CCGroup As String
Dim rng As Range
Sheets("CCTable").Select

    For Each r In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If Not IsEmpty(r.Value) Then
            If IsSheetExists(r.Value) Then
                Set ws = Sheets(CStr(r.Value))
                CCGroup = r.Value
                Sheets(CCGroup).Select
                Range("$E4").Value = CCGroup
                Range("$D3").Value = "=VLOOKUP(" & CCGroup & ",Table1[#All],2,FALSE)"
                Cells.Select
                Selection.Copy
            Else

                Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
                ws.Name = r.Value
                ws.Select
                Cells.Select
                Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
                CCGroup = r.Value
                Range("$E4").Value = CCGroup
                Range("$D3").Value = "=VLOOKUP(" & CCGroup & ",Table1[#All],2,FALSE)"
            End If
        End If
    Next
End Sub
 
 
Private Function IsSheetExists(ByVal txt As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(txt)
    IsSheetExists = (Err.Number = 0)
    On Error GoTo 0
End Function

**Royashencashy** · 2013-12-07, 09:14 AM

Здравствуйте участники форума, посоветуйте пожалуйста какой 3 джи интернет сейчас хороший, с МТС замучились, на работу интернет не провести, пользуемся только мобильным, что посоветуетеget-znanii ru/2789/dihatelnie-trenazheri-samozdrav-instruktsiya html ]