Run two macros for two different sheets

[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.

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

 

[Claus Busch]

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.

 

[Claus Busch]

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.

 

[L. Howard]

Hi again,



Am Thu, 24 Jul 2014 02:55:17 +0200 schrieb Claus Busch:






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

 

[GS]

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

 

[GS]

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

 

[L. Howard]

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...

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

 

[GS]

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

 

[L. Howard]

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

 

[GS]

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

 

[L. Howard]

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.

Got it.

Many thanks for the code and the "new to me" code concept.

Regards,
Howard

 

[GS]

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