?
Solved

Define named ranges while copying worksheets

Posted on 2011-03-23
4
Medium Priority
?
450 Views
Last Modified: 2012-05-11
I have a VBA script that copies active ranges in worksheets to corresponding worksheets in a new Workbook. -

For Counter = 1 To 7   ' number of worksheets to copy
        SourceWb.Worksheets(Counter).UsedRange.Copy DestWb.Worksheets(Counter).[a1]  
 ' get UsedRange from each source worksheet and copy to destination
    Next

What I need to do is modify this to set a named range for each Worksheet on the destination Workbook that only includes the active range that was copied.  Ideally I'd like to name the range 'worksheet name'_range.  I have PivotTables and charts on the destination Workbook that reference the named ranges.

0
Comment
Question by:Ed_CLP
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 

Expert Comment

by:NigelBulley
ID: 35202563
This is a clever bit of code for adding variablised Excel ranges but I do not fully understand your requirements. I hope this dynamic subroutine helps :-


Public Sub(intRowStart, intColStart, intRowEnd,intColEnd,Counter, strRangeName)

ActiveWorkbook.Names.Add Name:=strRangeName, RefersToR1C1:= _
"=" & Worksheets(Counter).Name & "!R" & Cstr(intRowStart) & "C" & Cstr(intColStart) & ":R" & CStr(intRowEnd) & "C" & intColEnd

End Sub

The sub routine was derived from the following macro recorded when adding a new named range :-

ActiveWorkbook.Names.Add Name:="new_name", RefersToR1C1:= _
"=Sheet1!R1C1:Rnew_name_counterC2"

If you enjoy electronic music please visit my music site which has between the www and com the following name areasontomakemusic.

Thats all folks
0
 

Expert Comment

by:NigelBulley
ID: 35202570
Darn it , I meant

Public Sub AddDynamicRange(intRowStart, intColStart, intRowEnd,intColEnd,Counter, strRangeName)

ActiveWorkbook.Names.Add Name:=strRangeName, RefersToR1C1:= _
"=" & Worksheets(Counter).Name & "!R" & Cstr(intRowStart) & "C" & Cstr(intColStart) & ":R" & CStr(intRowEnd) & "C" & intColEnd

End Sub
0
 
LVL 6

Accepted Solution

by:
rbrhodes earned 2000 total points
ID: 35202681
Her you go

Sub XferAndName()

Dim Ref As String
Dim Naem As String
Dim Addr As String
Dim destWB As Workbook
Dim sourceWB As Workbook

    Set sourceWB = Workbooks("book2")
    Set destWB = Workbooks("book1")

    'Number of worksheets to copy
    For Counter = 1 To 3
        'Get UsedRange from each source worksheet and copy to destination
        sourceWB.Worksheets(Counter).UsedRange.Copy destWB.Worksheets(Counter).[a1]
        'Create name for range
        Addr = sourceWB.Worksheets(Counter).UsedRange.Address(False, False)
        Addr = Application.WorksheetFunction.Substitute(Addr, ":", "_")
        Naem = destWB.Worksheets(Counter).Name & "_" & Addr
        'create name for refers to
        Ref = "='" & destWB.Worksheets(Counter).Name & "'!" & sourceWB.Worksheets(Counter).UsedRange.Address
        'Do name
        destWB.Names.Add Name:=Naem, RefersTo:=Ref
    Next

    'Cleanup
    Set destWB = Nothing
    Set sourceWB = Nothing

End Sub
0
 

Author Closing Comment

by:Ed_CLP
ID: 35202905
Perfect solution!!!
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question