Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro for multiple sheet ??
Hai Friends,
I have created a macro(to hide some rows). It is attached to a command button in sheet1. It is working fine. Now I want to run the same macro in sheet2 aslo without opening sheet2. Is there a solution for this ??? Thanks in advance --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro for multiple sheet ??
Somu,
It's hard to tell (without seeing the code) whether this will work for you but replace every cellreferende in your macro from ActiveSheet.Range("A1") or something like (and offcourse all other cellreferences as well ) to Worksheets("Sheet2").Range("A1") (In both cases you migth use Cells(1,1) instead of Range("A1") ). Better would be (if you always do both sheets together) to use a loop. For I = 1 to 2 Your macro with Worksheets(I) insttead of something like ActiveWorksheet or Worksheets("Sheet2") Next -- Regards, Auk Ales * Please reply to this newsgroup only * * I will not react on unsolicited e-mails * "somu " wrote in message ... Hai Friends, I have created a macro(to hide some rows). It is attached to a command button in sheet1. It is working fine. Now I want to run the same macro in sheet2 aslo without opening sheet2. Is there a solution for this ??? Thanks in advance --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro for multiple sheet ??
Thank u for your reply.
I am sending my code for your reference so that you can tell the exac solution this macro is to copy the data from two columns and paste it is colum "O" and hide all rows which are not having data (CHECK COLUM "O". Sub HideRows() Dim rgHide As Range, rw As Range Dim RST As String ' Reset value Dim OP As Integer 'Output value Dim RST_COL Dim OP_COL '--------------- FOR unchideing all the rows Range("A1:A200").Select Selection.EntireRow.Hidden = False '-------------------------- RST_COL = Range("AW5").Cells.Value 'take i/p values for reset OP_COL = Range("AW8").Cells.Value 'take i/p value for o/p '''''''''''''''''''''''''''''''''' '''''''''''''''''''''' 'CLEAR CELLS Dim i For i = 4 To No_Row If (Not Cells(i, 15).Value) Then Cells(i, 15).Value = "" End If 'Cells(i, 1).Value = "" Next i '''''''''''''''' 'copy data from reset to G For i = 4 To No_Row If Not Cells(i, RST_COL).Value Then Cells(i, 15).Value = Cells(i, RST_COL).Value End If 'Cells(i, 1).Value = "" Next i 'copy data from O/P to G For i = 4 To No_Row If (Cells(i, 15).Value = "") And (Not Cells(i, OP_COL).Value) Then Cells(i, 15).Value = Cells(i, OP_COL).Value End If 'Cells(i, 1).Value = "" Next i 'HIDE CELLS For Each rw In Range("O4:O200").Rows If Application.CountA(rw) = 0 Then If rgHide Is Nothing Then Set rgHide = rw Else Set rgHide = Union(rgHide, rw) End If End If Next rw If Not rgHide Is Nothing Then rgHide.EntireRow.Hidden = True End Su -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro for multiple sheet ??
Somu,
As far as I see in your code there is nothing in it what forces it to run on sheet1 only. You can do two things Which of the two you have (and want) to do is dependent of what you want. A) You want to run the code on both sheets by pressing once on the button on sheet1. B) You want to input somehow on what sheet the macro has to run if you press the button on 1. In the first case (A) : 1) Get the Dim i statement after 'CLEAR CELLS away (and put it direct after the DIM statement at the to of your program; at the same time change it to Dim i as Integer). 2) Then Add a statement (in the same area) Dim Sheetnr as Integer. 3) In front of your line : '--------------- FOR unchideing all the rows add a statement : For Sheetnr = 1 to 2 4) After your line : If Not rgHide Is Nothing Then rgHide.EntireRow.Hidden = True add a statement : Next Sheetnr (or just Next if you like that better) 5) In all you statements (except the comments) between the two mentioned above, replace Cells(.....) with Worksheets(Sheetnr).Cells(....) and : Range(....) with Worksheets(Sheetnr).Range(.....) This solution assumes that Sheet1 and Sheet2 are the 2 first sheets of your workbook. If not you will have within the For Next loop the value of Sheetnr and on the basis of give some variable (for instance) SheetName the value "Sheet1" or "Sheet2" and do as far as point 5 is concerned as in point 3 from the second solution (see next ) In the second case : 1) As 1) above 2) Add a statement Dim Sheetname and load it somehow (for instance with inputbox) with "Sheet1" or "Sheet2" 3) As 5 above, however now Worksheets(SheetName).Cells(....) or Worksheets(SheetName).Range(....) By the way : For both solutions you can replace Range("A1:A200").Select Selection.EntireRow.Hidden = False with Range("A1:A200").EntireRow.Hidden = False You don't have to select (or activate) a sheet or a range in order to perform actions on it. If you avoid selections your code speeds up as well .. Basicly that's what you are doing with both solutions above as well. You perform actions on sheet2 without selecting or activating it. To speed thing further up : Take a look (after everything is working well) in the help file for Application.Screenupdating as well. Succes. -- Regards, Auk Ales * Please reply to this newsgroup only * * I will not react on unsolicited e-mails * "somu " wrote in message ... Thank u for your reply. I am sending my code for your reference so that you can tell the exact solution this macro is to copy the data from two columns and paste it is column "O" and hide all rows which are not having data (CHECK COLUM "O". Sub HideRows() Dim rgHide As Range, rw As Range Dim RST As String ' Reset value Dim OP As Integer 'Output value Dim RST_COL Dim OP_COL '--------------- FOR unchideing all the rows Range("A1:A200").Select Selection.EntireRow.Hidden = False '-------------------------- RST_COL = Range("AW5").Cells.Value 'take i/p values for reset OP_COL = Range("AW8").Cells.Value 'take i/p value for o/p '''''''''''''''''''''''''''''''''' '''''''''''''''''''''' 'CLEAR CELLS Dim i For i = 4 To No_Row If (Not Cells(i, 15).Value) Then Cells(i, 15).Value = "" End If 'Cells(i, 1).Value = "" Next i '''''''''''''''' 'copy data from reset to G For i = 4 To No_Row If Not Cells(i, RST_COL).Value Then Cells(i, 15).Value = Cells(i, RST_COL).Value End If 'Cells(i, 1).Value = "" Next i 'copy data from O/P to G For i = 4 To No_Row If (Cells(i, 15).Value = "") And (Not Cells(i, OP_COL).Value) Then Cells(i, 15).Value = Cells(i, OP_COL).Value End If 'Cells(i, 1).Value = "" Next i 'HIDE CELLS For Each rw In Range("O4:O200").Rows If Application.CountA(rw) = 0 Then If rgHide Is Nothing Then Set rgHide = rw Else Set rgHide = Union(rgHide, rw) End If End If Next rw If Not rgHide Is Nothing Then rgHide.EntireRow.Hidden = True End Sub --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charts in Multiple Sheet in Excel | Charts and Charting in Excel | |||
multiple sheet macro | Excel Worksheet Functions | |||
Excel Multiple sheet updates | Excel Worksheet Functions | |||
Button with Macro to Email sheet to multiple addressees | Excel Discussion (Misc queries) | |||
how do I tie multiple worksheets to one sheet in Excel? | Excel Worksheet Functions |