If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Activate all sheets
Dave
That's the one! Can't fault it. Long may you select... George Dave Peterson wrote: If the selected cell was the one to start with and the activesheet was one of those Eng, Scot, Wales, I think I'd use something like: Option Explicit Sub testme01() Dim wks As Worksheet dim myAddr as string myAddr = activecell.address For Each wks In ActiveWorkbook.Worksheets(Array("Eng", "Scot", "Wales")) With wks .Select .range(myaddr).entirerow.insert .Range("Ad5:AE5").AutoFill _ Destination:=.Range("AD5:AE593"), Type:=xlFillDefault .Range("D5").Select End With Next wks End Sub I still don't like working with selection. George Gee wrote: Ok, I see your point about the the position of the cursor. So if the code grouped the worksheets, and the cursor was in the correct cell, on the 'first' sheet ("Eng") .... Sheets(Array("Eng", "Scot", "Wales")).Select Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets(Array("Eng", "Scot", "Wales")) With wks .Select Selection.EntireRow.Insert .Range("Ad5:AE5").AutoFill _ Destination:=.Range("AD5:AE593"), Type:=xlFillDefault .Range("D5").Select End With Next wks End Sub This seems to work as I want it to. Many thanks Dave for showing me the way, and for getting me to think a little for myself! George Dave Peterson wrote: It would scare me to leave it up to a cell I have selected on a worksheet that isn't active--I just don't keep track of where I leave the cursor when I change sheets. But... Option Explicit Sub testme01() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets(Array("Eng", "Scot", "Wales")) With wks .Select ActiveCell.EntireRow.Insert '-- changed .Range("Ad5:AE5").AutoFill _ Destination:=.Range("AD5:AE593"), Type:=xlFillDefault .Range("D5").Select End With Next wks End Sub George Gee wrote: Thanks Dave for your response. You code works fine! However, I do not know, and the end user does not know, where in the worksheet the new row(s) will be inserted! (At this moment). Can your code be altered to insert a new row, at (or just above) the selected cell, on all worksheets? Again, many thanks for your assistance with this. George Dave Peterson wrote: There are somethings that work with grouped sheets when you do them manually. But won't work when you do them via code. Selection.entirerow.insert worked fine when I did it manually, but didn't work via code. The autofill worked find manually and via code. I think I'd dump the grouped sheets and just process each sheet separately. This kind of thing should work: Option Explicit Sub testme01() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets(Array("Eng", "Scot", "Wales")) With wks .Select .Range("a999").EntireRow.Insert .Range("Ad5:AE5").AutoFill _ Destination:=.Range("AD5:AE593"), Type:=xlFillDefault .Range("D5").Select End With Next wks End Sub But since your selection.entirerow.insert relied on some other code--or the current selection, I couldn't tell what was going on. I just used A999--correct it to what you want. Ps. Very rarely do you have to select anything to work on it. But you do have to select the worksheet before you select a range (D5) on that worksheet. George Gee wrote: B. R.Ramachandran Many thanks for your response, however .... I do know how to do all that you are suggesting. I am trying to make a worksheet noddy-proof, for a user who wishes to use it to input data, and occasionally insert a new row, at different positions, but on *all* worksheets, and then to copy a selection to all rows in the worksheets. At the moment, I have a macro, that I cannot seem to get to work as I would like it to. As stated in my previous post, the enclosed macro groups all the sheets, and inserts a new row, but only on the first worksheet. Sheets(Array("Eng", "Scot", "Wales")).Select Selection.EntireRow.Insert ActiveWindow.SmallScroll ToRight:=19 Range("AD5:AE5").Select Selection.AutoFill Destination:=Range("AD5:AE593"), Type:=xlFillDefault Range("AD5:AE593").Select ActiveWindow.SmallScroll ToRight:=-19 ActiveWindow.ScrollRow = 5 Range("D5").Select End Sub Can I please ask? Is what I am trying to do achievable? Should enclosed macro work? If not, can someone please help me to change the macro, as needed? Many thanks George B. R.Ramachandran wrote: Hi, You can insert a new row in several sheets at the SAME position without having to invoke a macro. Select all the sheets where you want to insert a new row (you can select multiple sheets by holding the CTRL button and clicking on the sheet tabs at the bottom of the sheets), right-click on the row number (near the left-side border of the sheet) where you want to insert a new row and click 'Insert" in the drop-down list. A new row is inserted in every sheet selected. Now click on one of the sheet tabs to undo the multiple sheet selection. Regards, B. R. Ramachandran "George Gee" wrote: Hi I need help with the following macro: I am grouping all the worksheets together, and selecting the row, above which I wish to insert a new row. I want to insert a new row on all sheets, the enclosed macro at the moment is only inserting a new row on the first sheet. Selection.EntireRow.Insert Range("AD5:AE5").Select Selection.AutoFill Destination:=Range("AD5:AE594"), Type:=xlFillDefault Range("AD5:AE594").Select ActiveWindow.ScrollRow = 5 Range("D5").Select Can anyone help me with this? George Gee |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Autofilter Lists across Multiple Sheets, Maintain Correct Referenc | EDSTAFF | Worksheet Functions | 0 | November 14th, 2005 03:27 PM |
In 3 active sheets in wkbk, determine& display the # of sheets that have data | wrpalmer | General Discussion | 1 | November 4th, 2005 02:01 PM |
How do you activate Office 2003 by phone? | Vanguard10 | General Discussions | 2 | May 9th, 2005 07:51 PM |
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | RICHARD | Worksheet Functions | 1 | March 15th, 2005 05:49 PM |
(¯`·._.·[ Extract Data From Multiple Sheets ]·._.·´¯) | hyperdreamz | General Discussion | 1 | September 29th, 2004 04:03 PM |