CODEWITHSUNDEEP
excelvbasorting
JToone
JToone

Reputation: 1

VBA sort on similar sheets in a workbook without using named sheet based on months that will change based on current month

I need to copy a list from Column C that has blanks throughout the column to Column 0 sorted to remove the blanks. I need to do this on Multiple sheets represent the month (Jan, Feb, Mar, Apr....). The issue I run into is it uses: ActiveWorkbook.Worksheets("Jan") so if I do a do loop to get the other months (Feb, Mar....) then it won't work.

Essentially what I'm trying to get is a master list of all the names in column C from each month for a summary tab listing all the names from the various months. Depending on the month I run this the file will only have sheets for the months that have occurred.

Below is my code:

 'First Tab
     Columns("C:C").Select
        Selection.Copy
        Columns("O:O").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
     SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    With ActiveWorkbook.Worksheets("Jan").Sort
        .SetRange Range("O1:O1590")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

       Range("o:o").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort key1:=Range("o:o", Range("o:o").End(xlDown)), _
order1:=xlAscending, Header:=xlNo

'Add the managers to the next sheet
 Range("O1").Select

    Selection.End(xlUp).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    Range("p1").Select
    ActiveSheet.Paste


     End With

ActiveSheet.Next.Select

'''''''''''''''''''''
'''''''''''''''''''''


Do


Columns("C:C").Select
    Selection.Copy
    Columns("O:O").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    With ActiveWorkbook.Worksheets("Jan").Sort
        .SetRange Range("O1:O1590")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

       Range("o:o").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort key1:=Range("o:o", Range("o:o").End(xlDown)), _
order1:=xlAscending, Header:=xlNo

'Add the names to the next sheet

 Range("O1").Select

    Selection.End(xlUp).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    Range("p1").Select





   ' Selection.End(xlDown).Select
   ' Selection.End(xlUp).Select
    ActiveSheet.Paste

    ActiveSheet.Previous.Select
    Range("O1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveSheet.Next.Select
   Range("O1").Select
    Selection.End(xlDown).Select
     ActiveCell.Offset(1).Select
    ActiveSheet.Paste

    Selection.End(xlDown).Select
    Selection.End(xlDown).Select

   End With

If ActiveSheet.Next.Name = "Summary" Then
Exit Do

ElseIf ActiveSheet.Index <> Sheets.Count Then
ActiveSheet.Next.Select
Else
Exit Do
End If
Loop
ActiveSheet.Next.Select
Range("A1").Select


    Sheets("Summary").Select
    ActiveSheet.Previous.Select
    Columns("O:O").Select
    Selection.Copy
    ActiveSheet.Next.Select
    ActiveWindow.ScrollColumn = 2
    Columns("AC:AC").Select
    ActiveSheet.Paste
    Range("AC2").Select
    Selection.End(xlUp).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.ScrollColumn = 1
    Range("A3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    'ActiveSheet.Range("$A$2:$A$43").RemoveDuplicates Columns:=1, Header:=xlYesActiveSheet.Range.Cells("a1").Select


Sheets("Guide").Select
End Sub

Upvotes: 0

Views: 87

Answers (1)

PatricK
PatricK

Reputation: 6433

You can use Format() to get the worksheet names.

Below is an example of using it for your purpose. You will need to modify your code to work with inputting either the Worksheet name as String or the Worksheet Object itself. sName is what you are stuck on from this post. Example here uses the Worksheet Object Reference.

Option Explicit

Sub ProcessAllMonthsWorksheet()
    Dim iMonth As Integer, iYear As Integer, sName As String
    Dim oWS As Worksheet
    iYear = Year(Date)
    On Error Resume Next
    For iMonth = 1 To 12
        sName = Format(DateSerial(iYear, iMonth, 1), "mmm")
        Debug.Print "sName: " & sName
        Set oWS = ThisWorkbook.Worksheets(sName)
        If Not oWS Is Nothing Then ProcessMonthWorksheet oWS
        Set oWS = Nothing
    Next
End Sub

Private Sub ProcessMonthWorksheet(ByRef WorksheetObject As Worksheet)
    Debug.Print "Processing worksheet """ & WorksheetObject.Name & """"
    With WorksheetObject
        ' do your stuff with the worksheet
    End With
End Sub

Upvotes: 0

Related Questions