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
' get UsedRange from each source worksheet and copy to destination
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.