about of Arrays ? 1

[malpa]

Hi,

I´m reading a book to do something, but I have many doubts
I´m not an expert en VB.

My question

I want to navigate only for some sheets. I know the name of each sheet that I must to select.

I want to change my little program for this

Sub ccc ()

Worksheets.Add
ActiveSheet.Name = ("SUM")

For Each sheet In Array("sheet1", "sheet2", "sheet3", "sheet4",..))
...
...
Next sheet

ActiveSheet.Range("A3").AutoFilter Field:=1, Criteria1:="REGION"
ActiveSheet.Range("A3").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False

End Sub



The next program functions for me, but I want to reduce it, by one more little.

Sub Sum ()

Worksheets.Add
ActiveSheet.Name = ("SUM")

For i = 1 To Worksheets.Count

If Worksheets(i).Name = "sheet1" Then
'MsgBox Worksheets(i).Name
Sheets("sheet1").Activate
Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("SUM").Select
Range("A3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("SUM").Select

Elseif ..

Else

End If
Next i

ActiveSheet.Range("A3").AutoFilter Field:=1, Criteria1:="REGION"
ActiveSheet.Range("A3").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False

End Sub


Thanks for your assistance

Malpa

 

[SkipVought]

Hi,

I almost NEVER navigate from sheet to sheet in a program.

How Can I Make My Code Run Faster? faq707-4105

For instance

Sub ccc ()
    Dim ws as Worksheet

    set ws = Worksheets.Add
    ws.Name = "SUM"

For Each ws In Worksheets
  With ws
    Select case .name
      case "sheet1", "sheet2", "sheet3", "sheet4"
        .Range("A3").AutoFilter _
          Field:=1, Criteria1:="REGION"
        .Range("A3").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
    end select
  end with
Next sheet


End Sub

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[malpa]

Hi,
Thanks SkipVougth for your timely assistance

I changed the program by this one.
Question: How I do the same subroutine, using For each loop. Something similar to your posted program.

++++++++++++++++++++++++++++++++
Sub SUM()

Dim myshts(1 To 8) As String
Dim i As Integer
Dim sht As Worksheet
Dim lastrow As Long


myshts(1) = "AN"
myshts(2) = "CA"
myshts(3) = "CA"
myshts(4) = "CH"
myshts(5) = "NA"
myshts(6) = "QU"
myshts(7) = "RI"
myshts(8) = "VA"

Worksheets.Add
ActiveSheet.Name = ("SUM")
lastrow = 3
'MsgBox lastrow

For i = 0 To 7
Sheets(myshts(i + 1)).Activate
Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("SUM").Select
Cells(lastrow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
lastrow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row + 1
'MsgBox lastrow
'ActiveSheet.Paste Destination:=Cells(lastrow, 1)
'ActiveSheet.Paste
Application.CutCopyMode = False

Next i

ActiveSheet.Range("A3").AutoFilter Field:=1, Criteria1:="REGION"
ActiveSheet.Range("A3").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
Range("A3").Select

End Sub

Thanks

malpa

 

[SkipVought]

Sub SUM()
    Dim lastrow As Long
    Dim ws As Worksheet
    Dim wsSUM As Worksheet
    
    Set wsSUM = Worksheets.Add
    wsSUM.Name = "SUM"
    lastrow = 3
    
    For Each ws In Worksheets
        With ws
            Select Case .Name
                Case "AN", "CA", "CH", "NA", "QU", "RI", "VA"
                    .[A3].CurrentRegion.Copy
                    With wsSUM
                        lastrow = .[A1].CurrentRegion.Rows.Count + 1
                        .Cells(lastrow, 1).Paste
                        Application.CutCopyMode = False
                    End With
            End Select
        End With
    Next
    
    With wsSUM
        With .Range("A3")
            .AutoFilter Field:=1, Criteria1:="REGION"
            .CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
        .AutoFilterMode = False
        .Select
    End With
End Sub

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}