Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
MACROS IN MULTIPLE SHEETS COLT45 Excel Programming 0 April 13th 09 11:53 PM
macros over different sheets Robb.rich Excel Worksheet Functions 1 May 2nd 08 11:54 AM
Hidden sheets and macros mwc0914[_18_] Excel Programming 2 July 5th 06 04:55 PM
Limiting macros to certain sheets Giselle Excel Worksheet Functions 1 January 25th 06 05:23 AM
Macros on protected sheets? Karen Brown Excel Programming 4 November 21st 03 04:53 AM


All times are GMT +1. The time now is 12:08 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"