• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

VDB : Change of working sheet

I have written a "macro" for excell that make some changes on two given sheets. Both traitments are similar on the two sheets. Run as two separated macros, they works properly but if I put both macros in one, a part of the second traitement is done on the first sheet instead of the second sheet, even if I change the working sheet. A part of the second traitement is properly done on the right sheet but at the end of a "with" the second traitement is done on the first sheet????
Here is the code :

Sub SMSProfSkills()

Set ws = ActiveWorkbook.Sheets("Professional Skills")
    ws.AutoFilterMode = False

    ws.Columns("A:A").Delete Shift:=xlToLeft
    With ws.Rows("3:3")
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 90
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Cells.Select
    Selection.ColumnWidth = 3
    Columns("A:A").ColumnWidth = 7
    Columns("B:B").ColumnWidth = 20
    Columns("C:C").ColumnWidth = 15
    Columns("G:G").ColumnWidth = 5
    Range("A3").CurrentRegion.Select
    nbrows = Selection.Rows.Count - 3
    nbcols = Selection.Columns.Count - 7

    For i = 1 To nbrows
       For j = 1 To nbcols
       Set celltoclean = Range("G3").Offset(i, j)
       celltoclean.Select
       celltoclean.Value = celltoclean.Value
       Select Case celltoclean.Value
         Case "2"
           celltoclean.Cells.Interior.ColorIndex = 43
         Case "3"
           celltoclean.Cells.Interior.ColorIndex = 5
         Case "4"
           celltoclean.Cells.Interior.ColorIndex = 6
         Case "5"
           celltoclean.Cells.Interior.ColorIndex = 3
           
       End Select
       Next j
    Next i
    Range("A1").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$3"
        .PrintTitleColumns = "$A:$B"
    End With

    Rows("3:3").RowHeight = 160
End Sub

HERE is the second traitement
'
' Macro recorded 11/05/00

    Dim ws As Worksheet

    Set ws = ActiveWorkbook.Sheets("Product Skills")
    ws.AutoFilterMode = False

    ws.Columns("A:A").Delete Shift:=xlToLeft
    With ws.Rows("3:3")
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 90
        .ShrinkToFit = False
        .MergeCells = False
    End With            
AFTER THIS LINE, THE MACRO IS WORKING ON THE FIRST SHEET INSTEAD OF THE SECOND
    Cells.Select
     Selection.ColumnWidth = 3
    Columns("A:A").ColumnWidth = 7
    Columns("B:B").ColumnWidth = 20
    Columns("C:C").ColumnWidth = 15
    Columns("G:G").ColumnWidth = 5
    Range("A3").CurrentRegion.Select
    nbrows = Selection.Rows.Count - 3
    nbcols = Selection.Columns.Count - 7
'    celltoclean = Range("H4")
   
    For i = 1 To nbrows
       For j = 1 To nbcols
       Set celltoclean = Range("G3").Offset(i, j)
       celltoclean.Select
       celltoclean.Value = celltoclean.Value
       Select Case celltoclean.Value
         Case "2"
           celltoclean.Cells.Interior.ColorIndex = 43
         Case "3"
           celltoclean.Cells.Interior.ColorIndex = 5
         Case "4"
           celltoclean.Cells.Interior.ColorIndex = 6
         Case "5"
           celltoclean.Cells.Interior.ColorIndex = 3
           
       End Select
       Next j
    Next i
    Range("A1").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$3"
        .PrintTitleColumns = "$A:$B"
    End With

    Rows("3:3").RowHeight = 160
End Sub

0
pverbeeck
Asked:
pverbeeck
  • 2
1 Solution
 
arcusdCommented:
try putting a with Ws statement before the line....

with ws
                          Cells.Select
                         Selection.ColumnWidth = 3
                        Columns("A:A").ColumnWidth = 7
                        Columns("B:B").ColumnWidth = 20
                        Columns("C:C").ColumnWidth = 15
                        Columns("G:G").ColumnWidth = 5
                        Range("A3").CurrentRegion.Select
                        nbrows = Selection.Rows.Count - 3
                        nbcols = Selection.Columns.Count - 7
                    '    celltoclean = Range("H4")
                         
                        For i = 1 To nbrows
                           For j = 1 To nbcols
                           Set celltoclean = Range("G3").Offset(i, j)
                           celltoclean.Select
                           celltoclean.Value = celltoclean.Value
                           Select Case celltoclean.Value
                             Case "2"
                               celltoclean.Cells.Interior.ColorIndex = 43
                             Case "3"
                               celltoclean.Cells.Interior.ColorIndex = 5
                             Case "4"
                               celltoclean.Cells.Interior.ColorIndex = 6
                             Case "5"
                               celltoclean.Cells.Interior.ColorIndex = 3
                               
                           End Select
                           Next j
                        Next i
                        Range("A1").Select

end with
0
 
TimCotteeCommented:
pverbeeck, use ws.Activate immediately after the Set ws = ActiveWorkbook.Sheets("Sheetname") line in each section. This should ensure that the correct worksheet is active when the Cells.Select method is called.
0
 
pverbeeckAuthor Commented:
It doesn't work. But thanks for your help.
0
 
pverbeeckAuthor Commented:
Sorry timcottee, I did not see your comment before posting my previous one. But your response is working properly.
Thanks a lot
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now