processing all worksheets in a workbook.

[signoreexcel]

I am trying to do the same few things to all sheets in a worksheet. However, so far it seems to work only on 1 sheet. For example, for the first step, it cuts the first row and then pastes it one column to the right as it should, but instead of going on to the next worksheet it cuts again the first row and then pastes it another column to the right.

This is what I have:

Option Explicit

Public Sub ProcessDESeq2Results()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

           ' The six column titles in row 1 (columns A-F) are all shifted to the left
           ' by 1 column, so this part selects the 6 column titles in row 1(A1-F1),
           ' cuts them and then pastes them into B1-G1.

                Range("A1:F1").Select
                Selection.Cut
                Range("B1:G1").Select
                ActiveSheet.Paste
              


            ' This part then selects everything on the sheet from cell A2
            ' to end (somewhere near cell G32680) and then sorts by column G
            ' (which is now labelled 'padj' by above operation.) Ideally would be better to 
            ' select to end of sheet and not just to cell G32680

                Range("A2").Select
                Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
                ActiveWorkbook.Worksheets(ws).Sort.SortFields.Clear
                ActiveWorkbook.Worksheets(ws).Sort.SortFields.Add Key:= _
                Range("G2:G32679"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
                :=xlSortNormal
            With ActiveWorkbook.Worksheets(ws).Sort
                .SetRange Range("A1:G32679")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
              .Apply
           End With
         


         ' This part converts column G to Number, then somehow goes to where this column becomes
         ' 0.06 and deletes all rows below this

               Range("G2").Select
               Range(Selection, Selection.End(xlDown)).Select
               Selection.NumberFormat = "0.00"
               Range("I6").Select
               Print

      Next ws

End Sub

 

[iconiuss]

A few notes on your code.

In your For Each loop "wb" is becomeing the variable name for you Sheet objects. It doesn't activate each sheet.
So in the code:

Range("A1:F1").Select
Selection.Cut
Range("B1:G1").Select
ActiveSheet.Paste

Is always happening on the worksheet you last activated. (Same Sheet)
That code should read

ws.Range("A1:F1").Select
Selection.Cut
ws.Range("B1:G1:).Select
Selection.Paste

or better yet

ws.Range("A1:F1").cut ws.range("B1:G1")

Also where you have

[COLOR=#574123]ActiveWorkbook.Worksheets(ws).sort[/COLOR]

Just ws.sort is all you need

Hope that has helped.

 

[signoreexcel]

Thank you, iconiuss,

It is working much better now. This was very helpful and is getting me moving forward.