CODEWITHSUNDEEP
vbaexcel
user249611
user249611

Reputation: 65

Excel active sheet row count reflect previously active sheet

I have imported a csv file to sheet(4). I would like to select all rows and copy them to sheet(3). I would like to place the rows from sheet(3) on sheet(4) after any used rows. When I try and get the total number of rows used on sheet(3) "TotalRows" I get the number of rows imported on sheet(4). The reason is that if i have a fifth sheet(5). I would to place them at the end of rows on sheet(3).

For i = 4 To intNumberofSheets
        CurrentWorkbook.Sheets(i).Activate  'select the sheet
        'get the range to copy
        Set RangeToCopy = CurrentWorkbook.Sheets(i).Range(Range("A1"), _
                                                Range("O1").End(xlDown))

        CurrentWorkbook.Worksheets(3).Activate       'activate sheet3
        TotalRows = Sheets(3).UsedRange.Rows.Count   ' get used row count
        TotalRows = TotalRows + 1                    ' increment row by 1
        ColumnLetterNumber = "A" & TotalRows         ' A for column & last row
        RangeToCopy.AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Sheets("sheet3").Range(ColumnLetterNumber)

Next

Upvotes: 0

Views: 333

Answers (1)

L42
L42

Reputation: 19727

Why don't you try it simpler and more explicit. HTH.

Dim ws As Worksheet, tws As Worksheet
Set tws = CurrentWorkbook.Sheets("Sheet3")
For Each ws In CurrentWorkbook
    If ws.Name <> "Sheet3" Then
        ws.UsedRange.Copy tws.Range("A" & tws.Rows.Count).End(xlUp).Offset(1, 0)
    End If
Next

Upvotes: 1

Related Questions