Search code examples
excelexcel-2011vba

Macro to copy worksheet irrespective of worksheet name?

I export lots of information to a .csv file then download/open for manipulation. Each export is sequentially numbered (eg export (22).csv, export (23).csv etc hence the worksheet is also given the same name/number.

For each .csv file that I open, I would like a macro to do a quick filter, count and copy so that I can keep one complete worksheet before manipulation and work on a second worksheet. In my macro, I have:

Sub Export()
'
' Export Macro
'

'
    Range("A1").Select
    Selection.AutoFilter
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    Range("A1").Select
    Selection.End(xlDown).Select
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(3,R[-4]C:R[-1]C)"
    Range("A7").Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Range("A2").Select
    Sheets("export (87).csv").Select
    Sheets("export (87).csv").Copy After:=Sheets(1)
    Range("A1").Select
End Sub

This all works correctly for export (87).csv but then fails for the next file, export (88).csv.

How can I make the macro select the first sheet and copy to the end irrespective of the sheet name?

I have tried using:

Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Sheets(1)

Sheets("Sheets1").Select
Sheets("Sheets1").Copy After:=Sheets(1)

I get a RT Error 9 and

Sheets(Sheet1).Select
Sheets(Sheet1).Copy After:=Sheets(1)

I get a RT error 13

Thanking all in advance...

Solution

  • This part is causing the error

    Sheets(Sheet1).Select
Sheets(Sheet1).Copy After:=Sheets(1)

    The correct syntax is:

    Activeworkbook.Worksheets(1).Activate
ActiveSheet.Copy After:=ActiveSheet

    EDIT: Change the code so it does not use the name of the sheet