Copy adjacent Sheet and name from a list

[Claus Busch]

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

 

[Howard]

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.

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

 

[Claus Busch]

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