Home |
Search |
Today's Posts |
#41
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy adjacent Sheet and name from a list
On Tuesday, April 9, 2013 2:55:46 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Tue, 9 Apr 2013 02:47:53 -0700 (PDT) schrieb Howard: I tried the code in the sheet vb editor and it deleted the sheets but the names and formulas to the right remain intact. that is when the selected cells are not connected. Place the code in a standard module. Try: Sub DeleteSheets() Dim rngC As Range Application.DisplayAlerts = False For Each rngC In Selection Sheets(rngC.Text).Delete rngC.Resize(, 5).ClearContents Next Range("C8:G32").Sort key1:=[C9], order1:=xlAscending, Header:=xlYes Application.DisplayAlerts = True End Sub Regards Claus Busch I'm having success with this code, which seems to be identical to the previous. Makes me think I may be doing stuff at my end and getting errors and maybe it is not the code at all. Forgetting to select the sheet names to be selected before running the code is one example. I'm thinking I see a problem when I select from a list of 10 names, say the 4th, 7th, 9th and run delete code it kinda messes up, but I need to gather real specifics before I come to you with that. Using single digit numbers as sheet names may also be a problem. But applying strict do's and don't rules for use of codes to the end user seems reasonable to me. Thanks, Claus I'll bat it around |
#42
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy adjacent Sheet and name from a list
Hi Howard,
Am Tue, 9 Apr 2013 10:36:06 -0700 (PDT) schrieb Howard: I'm thinking I see a problem when I select from a list of 10 names, say the 4th, 7th, 9th and run delete code it kinda messes up, but I need to gather real specifics before I come to you with that. Using single digit numbers as sheet names may also be a problem. But applying strict do's and don't rules for use of codes to the end user seems reasonable to me. I tested the code in non adjacent cells. And it works. To eliminate the empty rows the last line will sort the range. Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#43
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy adjacent Sheet and name from a list
On Tuesday, April 9, 2013 9:15:39 AM UTC-7, GS wrote:
The col name defs are as follows... colC: "Sheetname" RefersTo: =Summary!$C9 colD: "LabHrs" RefersTo: =Summary!$D9 colE: "LabCost" RefersTo: =Summary!$E9 colF: "MatlCost" RefersTo: =Summary!$F9 colG: "Sell" RefersTo: =Summary!$G9 colI: "SellPlus" RefersTo: =Summary!$CI9 ..as defined after selecting any cell in row9. The total (Q36) is named "Total". Its RefersTo is fully absolute. The col formulas for the fixed table are as follows: colD: =IF(LEN(Sheetname),INDIRECT("'"&Sheetname&"'!G7"), "") colE: =IF(LEN(Sheetname),INDIRECT("'"&Sheetname&"'!H7"), "") colF: =IF(LEN(Sheetname),INDIRECT("'"&Sheetname&"'!I7"), "") colG: =IF(LEN(Sheetname),INDIRECT("'"&Sheetname&"'!J7"), "") colI: =IF(LEN(Sell),Sell+(Total/24),"") Given the structure of this sheet 'as is', adding new rows to the table will need to be done manually. I probably would have designed this sheet so a blank row could be stored (hidden) at the top of the sheet so I can insert as needed for adding more names. Unfortunately, for this to work would require relocating the area to the right of the table so it's above the table in frozen pane area. This approach would make removing names a simple matter of deleting entire rows, leaving no required extraneous cleanup processing to do. If you're interested to review a working copy of this let me know where to send/upload a file. -- Garry For sure I would love to see your rendetion of this project. I am not the end user of all this, I'm trying to help out someone just like you are trying to help me. Top of the line advice is most welcome even if I'm not fully capable of following it. I'm at: lhkittle @ comcast . net Remove the spaces Howard |
#44
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy adjacent Sheet and name from a list
Hi Howard,
Am Tue, 9 Apr 2013 10:36:06 -0700 (PDT) schrieb Howard: I'm thinking I see a problem when I select from a list of 10 names, say the 4th, 7th, 9th and run delete code it kinda messes up, but I need to gather real specifics before I come to you with that. Using single digit numbers as sheet names may also be a problem. But applying strict do's and don't rules for use of codes to the end user seems reasonable to me. you also can dump the selected cells in an array, group the sheets with the selected names and delete them at once: Sub ShDelete() Dim i As Integer Dim myArr() As Variant Dim rngC As Range Application.DisplayAlerts = False ReDim myArr(Selection.Cells.Count - 1) For Each rngC In Selection myArr(i) = rngC.Text rngC.Resize(, 5).ClearContents i = i + 1 Next Sheets(myArr()).Select ActiveWindow.SelectedSheets.Delete Range("C8:G32").Sort key1:=[C9], order1:=xlAscending, _ Header:=xlYes Application.DisplayAlerts = True End Sub Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i have a list i need to copy onto another sheet one by one | Excel Discussion (Misc queries) | |||
find IDs in another sheet, copy paste non-adjacent data on orig sh | Excel Programming | |||
Copy Paste from Class Sheet to Filtered List on Combined Sheet | Excel Programming | |||
When data match, copy adjacent value to adjacent column | Excel Worksheet Functions | |||
how to find and copy values on sheet 2, based on a list on sheet 1 | Excel Programming |