Combine 3 Macros

[LiAD]

Evening,

I have three macros which i need to run off one button. The first two run
fine but the third wont run. Can anyone tell me what I need to change to get
them all to run simultaneously?

Thanks
LiAD

Sub CreateComposition()
'AM to DJ
Dim lngRow As Long
For lngRow = 237 To 437
Call CopySpecialCells(lngRow, lngRow + 202)
Next
End Sub

Sub CopySpecialCells(lngSourceRow As Long, lngTargetRow As Long)
lngTemp = 39
For lngCol = 39 To 105
If IsNumeric(Cells(lngSourceRow, lngCol)) = False Then
Cells(lngTargetRow, lngTemp) = Cells(lngSourceRow, lngCol)
lngTemp = lngTemp + 1
End If
Next
End Sub

Sub FillPlanningSheet()
'
Columns("N:N").Select
Selection.EntireColumn.Hidden = True
Selection.AutoFilter Field:=5, Criteria1:="CB190"
Columns("F:F").Select
Selection.EntireColumn.Hidden = True
Range("C48:O250").Select
Selection.Copy
Sheets("CB190").Select
Range("D10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=10
Sheets("Base Planif.").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
Range("R48:R250").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CB190").Select
Range("N10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Base Planif.").Select
Range("P48250").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CB190").Select
Range("U10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Sheets("Base Planif.").Select
Application.CutCopyMode = False
ActiveWindow.SmallScroll ToRight:=-6
Columns("M:O").Select
Selection.EntireColumn.Hidden = False
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("C:G").Select
Selection.EntireColumn.Hidden = False
Columns("D").Select
Selection.EntireColumn.Hidden = True
Selection.AutoFilter Field:=5
Range("A1").Select
End Sub

 

[Gary''s Student]

Have the first call the third:

Sub CreateComposition()
'AM to DJ
Dim lngRow As Long
For lngRow = 237 To 437
Call CopySpecialCells(lngRow, lngRow + 202)
Next
Call FillPlanningSheet
End Sub

 

[Rick Rothstein]

A couple of comments about your code. These...

ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13

can be replaced with this...

ActiveWindow.ScrollColumn = 13

There is no reason to scroll one column (or sometimes 2 columns) at a
time... just scroll to the column you want. Same goes for this...

ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1

You don't have to cound down one column at a time either; just specify the
column you want to scroll to...

ActiveWindow.ScrollColumn = 1

Also, you don't have to select cells or ranges in order to work with them as
you did with this...

Columns("N:N").Select
Selection.EntireColumn.Hidden = True

This would work as well...

Columns("N:N").EntireColumn.Hidden = True

Perhaps this previous posting of mine (a response to another person using
Select/Selection type constructions) will be of some help to you in your
future programming...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever>

you can almost always do this instead...

Range("A1").<whatever>

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.

 

[LiAD]

Thanks a lot for the hints. Great to i can learn.

As I'm sure u guessed I'm a real VBA novice!

Cheers

 

[LiAD]

Cheers

Have the first call the third:

Sub CreateComposition()
'AM to DJ
Dim lngRow As Long
For lngRow = 237 To 437
Call CopySpecialCells(lngRow, lngRow + 202)
Next
Call FillPlanningSheet
End Sub