Posts by rdperkins

Re: Copy Named Range from one workbook to another

That's what I've done. Everything is working except one major point - I want to copy a named range from one workbook to another. WorkbookSource.Range("name_of_named_range") doesn't work because .Range isn't a method or property of workbooks, only worksheets. I think there may be a way to refer to named ranges in a workbook, regardless of which sheet they are on and that is what I'm looking for.

Re: Copy Named Range from one workbook to another

Quote from royUK;692820

Why not create a proper template, then you can tell each user to use the template which you can update when necessary

Thanks, but this is what I do when I make changes - send them a blank template. Problem is, they each still have the 30-90 minutes of copying data from the previous report to the new template as much of the data is static, only being appended each month.

I have a monthly report containing numerous sheets and tons of code. Occasionally I am asked to add a feature or update a chart. The trouble comes when trying to have 20-25 different users migrate data from their old versions to new ones. This takes from 30-90 minutes each depending on the user's needs. My plan is to copy named ranges from WbSource (the previous monthly report) to WbTarget (the new version). Assume the code will reside in WbTarget (it will obviously be in both after the first cycle).

I've worked out getting the file path and name from the user and setting the value for WbSource, and can loop through a table containing the names of all the ranges to be copied. Unfortunately I can't use workbook.range() to get select the range to be copied because range is a worksheet property. Aside from adding a SheetName column in the table with the range names to be copied, is there a way to 'select' a range in a workbook?

TIA.

Re: Copy new value to adjacent cell if value in a specific Table range changes

I solved my original problem another/simpler way. Basically I had a line graph showing cumulative hours expended per month against planned and forecast. I wanted to automatically update the forecast value to equal the expended for months that were completed and only show the forecast for future months. I'm just asking the users to copy-paste the actual value each month to the forecast cell instead of trying to do it in code.

Back to the first question. Excel takes care of adjusting absolute range references from a table in the formulas referring to the range elsewhere when the table changes dimensions. If it doesn't do this in the code, but you named the data range in the table, would Excel update the named range when the table changes sizes? If so I could refer to a range by name in code.

Excel automatically adjusts formulas in a workbook which refer to a Table range (one created by using Insert-Table) such as $A$36:$H$55 if the number of rows in the table changes. This includes adjusting the formulas in the Table range itself. If I hard-code a range in code which is part of that table, for instance $D$36:$D$55, does Excel also adjust the VBA code?

If so, then how do I set the value in another cell in the table (in column 'F') when a value in the range $D$36:$D$55 changes?

If not, I'll have to ask the user to copy and paste the value.

Thanks.

I have code that loops through the sheets in my workbook, then the tables on each sheet and takes action on all tables except one. I want to add a bunch of new tables which will require a different action. Roughly, my code now is:

For Each oSh In ActiveWorkbook.Sheets
    For Each tbl In oSh.ListObjects
         If tbl.Name <> "TableCalcs" Then
              Do something
         End If
    Next tbl
Next oSh

What I would like to do is replace the for each table line with something like For Each tbl in A TABLE ARRAY OR LIST (table1, table 4, table 5). Is this possible or should I use a select case construction?

TIA

Re: Re-Protect Sheet on loosing focus

Thanks. I'll try to remember that.
I had a problem getting it to work. The problem was that I was exiting the sheet via a code routine, and that seemed to bypass the deactivate event. I put the code at the top of my exit (to menu) routine and it worked. Any ideas on why the sheet.deactivate event didn't trigger?

I want to enable most of the sheets in my workbook, without a password, to protect the formatting and formulas from accidentally being overwritten or changed. However I do want to allow the users to make changes when they are intending to do so.

I have first un-locked the cells I allow data entry in, then protected the worksheet.
The user would then right-click the tab and unprotect the sheet, then make their changes.
When the user exits the sheet or workbook, I want to re-enable protection on the sheet.

I have the following procedure in a module
' begin code

Sub EnableActiveSheetProtection()
    ActiveSheet.Protect UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingRows:=True, AllowDeletingRows:=True, AllowFiltering:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

'end code

What worksheet event or workbook event do I call this routine from to do what I want?

Thanks,

Robert

Re: VBA Code to sort worksheets based on a pre-sorted named-range

Brilliant! Many thanks and kudos.
I believe what you are doing is sorting the last sheet in place, then next to last.... Is this correct?

Re: VBA Code to sort worksheets based on a pre-sorted named-range

Might be. I get a subscript out of range on "Sheets(rng(i).Value).Move after:=rng.Parent"

Re: VBA Code to sort worksheets based on a pre-sorted named-range

The 'Contents' page (menu) containing the range "WorksheetNames" is the first sheet and won't be sorted.
Next come 23 worksheets that will be sorted.
Finally there are 4 non-sorted worksheets that will always stay at the end.

Re: VBA Code to sort worksheets based on a pre-sorted named-range

Dear Jindon,

Works great in your worksheet. I guess I wasn't awake at 4:00AM when I tried it the first time. I do need a bit more help though. My list isn't on Sheet1 starting in cell A1. It is in a named range called "WorksheetNames" on the first worksheet in the workbook, one that like Sheet1 in your example won't be sorted. It also doesn't start in A1 and is subject to move as I may adjust the layout on that page in the future. One other thing that might be important is that the first sheet is not the only one not being sorted. I have about 4 or 5 sheets at the end that I exclude from printing and which will never be part of the WorksheetNames list.

What changes need to be made in your code to use the named range instead of cell A1 on Sheet1? I've taken a stab at he in the following code but I get a subscript out of range error.

[Start of Code]
Sub TestSort()
Dim r As Range, cnt As Long, Rng As Range
Set Rng = Range("WorksheetNames")
cnt = Sheet20.Range("PrintableSheetCount").Value 'a count formula in this named cell contains the correct number of sheetnames to be re-ordered.
Rng.Select
For Each r In Rng
Sheets(r.Value).Move after:=Sheets(cnt)
Next
Sheets("Setup").Activate
End Sub
[End of Code]
[TABLE="width: 168"]

[/TABLE]

Re: VBA Code to sort worksheets based on a pre-sorted named-range

I tried Dangle's code but must not have changed the right bits. I tried Jindon's code, and it worked in his example for the sheets named sheet1, sheet2, etc., but my sheets have been renamed. I renamed one of the sheets in his example and on page 1 of the list supplying the new order, but it didn't work anymore. Is there a way to change the code to reference the sheet name property instead of Sheet1, etc.?

I have a range containing the worksheet names in my workbook, about 30 sheets. I want to allow the users to sort the sheets in the order they like for printing. I have another range containing the worksheet names and an 'order' column, with 1-n numbers. The user would just change the numbers in the sort order, and my code then sorts that range and copies the sorted worksheet names into the 1d range on another page. So far so good. However next I need code to actually re-order the worksheets according to the sorted range. I found a function on a lot of sites made by C.Pearson that accepts an array of names, but it gives no subroutine showing how to create the array or invoke the function. I also saw comments that it was a bit convoluted, but can't comment. Hopefully someone either has solved this problem differently and can post a solution, or has the code and instructions to use Pearson's "SortWorksheetsByNameArray" function.

TIA,

RPerkins

Re: Code to find true Sheetname

Great replies, and quick. It got me part of the way there. What I'm trying to do is check to see if the sheetname is changed by a user, and if so to change a menu name on a 'Contents' page accordingly. My strategy is this:
1. On Workbook Open to declare a public constant PrevShtName and assign it the value of "Sheet1", which is my 'Contents' page and probably won't get renamed.
2. On each of the other worksheets, set the value of PrevShtName = ActiveSheet.Codename as the sheets are selected.
3. In a Workbook SheetDeactivate event, check the final PrevShtName.Name against the contents menu string, and if it has changed, to change the contents menu string to match.

Questions:
A. Does it look like the above might work? If so,
B. Where and when do I declare the PrevShtName variable and should it be a variable or constant?
C. Should it be a variant or a string? Public or Private?
D. I originally planned only to check the value of 5 sheets, and hard-coded the following in the WorksheetDeactivate event, and it worked well. However I thought it might be best to give the users the ability to rename other sheets as well.

TabName = Sheet10.Name 'User2 sheet name change
MenuName = Sheet1.Range("E8").Value
MenuNameLen = Len(Sheet1.Range("E8").Value) - 4
MenuName = Right(MenuName, MenuNameLen)
If MenuName <> TabName Then
Sheet1.Range("E8").Value = "18. " & TabName
End If

E. I thought instead of hard-coding each of the 20 other sheets like this that I could simplify the coding into either a select case (Select PrevShtName), or even a short routine which would cycle through the sheets somehow. I'm also wondering if it might not be best to create a table with CodeName and Name values for each of the sheets.

Comments?

Hi,

I have a spreadsheet with many sheets, all of which I've renamed. I need the code to determine the 'true' sheetname, i.e. Sheet1, Sheet2, etc., instead of the name I've applied. So far my searches have only turned up Activesheet.name, which doesn't give me what I need.