Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • 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
 
TimCotteeHead of Software ServicesCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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