Results 1 to 4 of 4

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

  1. #1

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

    Register for a FREE account, and/
    or Log in to avoid these ads!


    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.



  2. #2

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

    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
        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
                    Range("$E4").Value = CCGroup
                    Range("$D3").Value = "=VLOOKUP(" & CCGroup & ",Table1[#All],2,FALSE)"
                    Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
                    ws.Name = r.Value
                    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
    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

  3. #3

    Какой 3 джи инт

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

  4. #4

    Sorry but I cannot read Russian.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts