Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run two macros for two different sheets
Both codes in a standard module at present.
If I run the first one for sheet 1, I want to run the second one for sheet 2. Calling the second code from the first does not work. Strange to me?? I have also tried to combine both in a single macro using With Sheets(Sheet2) / End With to get the code to do its job on sheet 2, but no go. End goal is to have them in a worksheet change event macro so when sheet 1 is changed sheet 2 is also changed. Thanks. Howard Sub TopToBottom_A() '/ Moves A1 Dim LastRow As Long LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False Cells(1, 1).Cut Cells(Rows.Count, "A").End(xlUp)(2) Range("A2").Resize(LastRow, 2).Cut Cells(1, 1) Application.ScreenUpdating = True End Sub Sub TopToBottom_AB() '/ Moves A1 & B1 Dim LastRow As Long LastRow = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row Application.ScreenUpdating = False Cells(1, 1).Resize(1, 2).Cut Cells(Rows.Count, "A").End(xlUp)(2) Range("A2").Resize(LastRow, 2).Cut Cells(1, 1) Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run two macros for two different sheets
Hi Howard,
Am Wed, 23 Jul 2014 17:42:51 -0700 (PDT) schrieb L. Howard: Both codes in a standard module at present. If I run the first one for sheet 1, I want to run the second one for sheet 2. try: Sub TopToBottom_A() '/ Moves both 'Claus B. Dim LastRow1 As Long Dim LastRow2 As Long LastRow1 = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row LastRow2 = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row Application.ScreenUpdating = False With Sheets("Sheet1") .Cells(1, 1).Cut .Cells(Rows.Count, "A").End(xlUp)(2) .Range("A2").Resize(LastRow1 + 1, 1).Cut .Cells(1, 1) End With With Sheets("Sheet2") .Cells(1, 1).Resize(1, 2).Cut .Cells(Rows.Count, "A").End(xlUp)(2) .Range("A2").Resize(LastRow2 + 1, 2).Cut .Cells(1, 1) End With Application.ScreenUpdating = True End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run two macros for two different sheets
Hi again,
Am Thu, 24 Jul 2014 02:55:17 +0200 schrieb Claus Busch: try: better try: Sub TopToBottom() 'Claus B. Dim LRow1 As Range, LRow2 As Range With Sheets("Sheet1") Set LRow1 = .Cells(Rows.Count, 1).End(xlUp) .Range("A1").Cut LRow1.Offset(1, 0).Insert Shift:=xlDown End With With Sheets("Sheet2") Set LRow2 = .Cells(Rows.Count, 1).End(xlUp) .Range("A1:B1").Cut LRow2.Offset(1, 0).Insert Shift:=xlDown End With End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run two macros for two different sheets
On Wednesday, July 23, 2014 6:03:09 PM UTC-7, Claus Busch wrote:
Hi again, Am Thu, 24 Jul 2014 02:55:17 +0200 schrieb Claus Busch: try: better try: Sub TopToBottom() 'Claus B. Dim LRow1 As Range, LRow2 As Range With Sheets("Sheet1") Set LRow1 = .Cells(Rows.Count, 1).End(xlUp) .Range("A1").Cut LRow1.Offset(1, 0).Insert Shift:=xlDown End With With Sheets("Sheet2") Set LRow2 = .Cells(Rows.Count, 1).End(xlUp) .Range("A1:B1").Cut LRow2.Offset(1, 0).Insert Shift:=xlDown End With End Sub Regards Claus B. That does it nicely. I was having a mental block making this work and it is really fairly simple. Thanks for bailing me out on something I should have been able to do myself. Regards, Howard |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run two macros for two different sheets
I have also tried to combine both in a single macro using
With Sheets(Sheet2) / End With to get the code to do its job on sheet 2, but no go. End goal is to have them in a worksheet change event macro so when sheet 1 is changed sheet 2 is also changed. Try... Sub TopToBottom(SourceRng As Range, Col$, Size&) Dim lRow& With SourceRng.Parent lRow = .Cells(.Rows.Count, Col).End(xlUp).Row SourceRng.Cut .Cells(.Rows.Count, "A").End(xlUp)(2) .Range("A2").Resize(lRow, Size).Cut .Cells(1, 1) End With 'SourceRng.Parent End Sub Sub Test_TopToBottom() Application.ScreenUpdating = False TopToBottom Sheets("Sheet1").Cells(1, 1), "A", 2 TopToBottom Sheets("Sheet2").Cells(1, 1).Resize(1, 2), "B", 2 Application.ScreenUpdating = True End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run two macros for two different sheets
Note that whenever you need a reusable procedure to process
conditionally, it must implement input args that pass values provided by the caller! I'd prefer that the reusable procedure *not* use hard-coded range refs but I ignored this to keep the sample simple. Normally, such a procedure would be used to provide global app functionality and so more args would be used. Since your hard-coded range refs are identical I didn't see any point to make things more complex than needed... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run two macros for two different sheets
On Thursday, July 24, 2014 8:59:01 AM UTC-7, GS wrote:
Note that whenever you need a reusable procedure to process conditionally, it must implement input args that pass values provided by the caller! I'd prefer that the reusable procedure *not* use hard-coded range refs but I ignored this to keep the sample simple. Normally, such a procedure would be used to provide global app functionality and so more args would be used. Since your hard-coded range refs are identical I didn't see any point to make things more complex than needed... -- Garry Hi Garry, Very interesting! I don't think I've seen a concept like that, or if I did, I did not understand what I was looking at. And I don't fully grasp all of the magic of this little gem. One thing that could be a problem is where "Size" in this line returns 2. .Range("A2").Resize(lRow, Size).Cut .Cells(1, 1) On sheet 1 if there is data in column B it is deleted in row 1 each time the code is run. (I can't figure how Size is set to 2) But the 2 is good for sheet 2 where there is two columns of data. Seems unlikely Size could return the number of columns the user wants per sheet, for example: Sheet 1 Size = 1 Sheet 2 Size = 3 Sheet 3 Size = 2 Sheet 4 Size = .EntireRow So, with these examples here, would the user need to "Input" by some manner the number of columns? Is this where named ranges come into play? Or InputBoxs? Howard |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run two macros for two different sheets
Very interesting! I don't think I've seen a concept like that, or if
I did, I did not understand what I was looking at. And I don't fully grasp all of the magic of this little gem. One thing that could be a problem is where "Size" in this line returns 2. .Range("A2").Resize(lRow, Size).Cut .Cells(1, 1) On sheet 1 if there is data in column B it is deleted in row 1 each time the code is run. (I can't figure how Size is set to 2) 'Size' is the last input arg of the TopToBottom procedure. It's there only to facilitate your use of 'Resize()' to define the range to 'Cut'. But the 2 is good for sheet 2 where there is two columns of data. Seems unlikely Size could return the number of columns the user wants per sheet, for example: Sheet 1 Size = 1 Sheet 2 Size = 3 Sheet 3 Size = 2 Sheet 4 Size = .EntireRow So, with these examples here, would the user need to "Input" by some manner the number of columns? Is this where named ranges come into play? Or InputBoxs? No, not in this case. The caller could be rewritten as follows to be more clear about what it's doing... Sub Test_TopToBottom() Application.ScreenUpdating = False TopToBottom SourceRng:=Sheets("Sheet1").Cells(1, 1), _ Col:="A", Size:=2 TopToBottom SourceRng:=Sheets("Sheet2").Cells(1, 1).Resize(1, 2), _ Col:="B", Size:=2 Application.ScreenUpdating = True End Sub ...where the arg 'Col' passes the column label of the col to use for finding 'lRow' in 'TopToBottom' for the 2nd 'Cut'. The var naming was 'quick-n-dirty' fashion and certainly would be more descriptive in a finished project... Sub TopToBottom(rngSource As Range, sColLabel$, lColCount&) Dim lRowCount& With rngSource.Parent lRowCount = .Cells(.Rows.Count, sColLabel).End(xlUp).Row rngSource.Cut .Cells(.Rows.Count, sColLabel).End(xlUp)(2) .Range("A2").Resize(lRowCount, lColCount).Cut .Cells(1, 1) End With 'rngSource.Parent End Sub ...or instead of lRowCount/lColCount I might go with lMaxRows/lMaxCols! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run two macros for two different sheets
No, not in this case. The caller could be rewritten as follows to be
more clear about what it's doing... Sub Test_TopToBottom() Application.ScreenUpdating = False TopToBottom SourceRng:=Sheets("Sheet1").Cells(1, 1), _ Col:="A", Size:=2 TopToBottom SourceRng:=Sheets("Sheet2").Cells(1, 1).Resize(1, 2), _ Col:="B", Size:=2 Application.ScreenUpdating = True End Sub Probably a typo... I changed the line for sheet 1 as below (Size:=1) and all works fine. TopToBottom SourceRng:=Sheets("Sheet1").Cells(1, 1), _ Col:="A", Size:=1 How would Size = EntireRow be stated? Howard |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run two macros for two different sheets
How would Size = EntireRow be stated?
Can't say since EntireRow doesn't 'logically' relate to 'Size' in the context being used. EntireRow is a range ref to a specified range... Cells(1, 1).EntireRow Range("A1").EntireRow Rows(1) ...all ref the same row same as... Range("A1:A3").EntireRow Range(Cells(1), Cells(3)).EntireRow Cells(1, 1).Resize(3).EntireRow Range("A1").Resize(3).EntireRow ...all ref the same range of rows. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run two macros for two different sheets
On Thursday, July 24, 2014 3:43:58 PM UTC-7, GS wrote:
How would Size = EntireRow be stated? Can't say since EntireRow doesn't 'logically' relate to 'Size' in the context being used. EntireRow is a range ref to a specified range... Cells(1, 1).EntireRow Range("A1").EntireRow Rows(1) ..all ref the same row same as... Range("A1:A3").EntireRow Range(Cells(1), Cells(3)).EntireRow Cells(1, 1).Resize(3).EntireRow Range("A1").Resize(3).EntireRow ..all ref the same range of rows. -- Garry Got it. Many thanks for the code and the "new to me" code concept. Regards, Howard |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run two macros for two different sheets
Always glad to help...
-- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MACROS IN MULTIPLE SHEETS | Excel Programming | |||
macros over different sheets | Excel Worksheet Functions | |||
Hidden sheets and macros | Excel Programming | |||
Limiting macros to certain sheets | Excel Worksheet Functions | |||
Macros on protected sheets? | Excel Programming |