0

Following code I'am getting data from multiple sheets into a master workbook. When run the macro I have the error: For without Next.

Sub copydata()

Dim FolderPath As String, Filepath As String, Filename As String

FolderPath = "C:\Users\stefan.georgescu\Desktop\Verificari\"

Filepath = FolderPath & "*.xlsx"

Filename = Dir(Filepath)

Dim erow As Long, lastrow As Long, lastcolumn As Long

Do While Filename <> ""

    Dim wb As Workbook

    Set wb = Workbooks.Open(FolderPath & Filename)

    Do
        For counter = 1 To 5

        Dim ws As Worksheet

        Set wb = ActiveWorkbook

        Do
            For Each ws In wb.Worksheets

                wb.Worksheets(counter).Activate
                lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
                lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
                Range(Cells(2, 1), Cells(lastrow, lastcolumn)).Copy
                'Sheets(“Sheet1”).Select
                Workbooks("VerificariCEgeneral.xlsm").Worksheets(counter).Activate
                erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                Cells(erow, 1).Select
                ActiveSheet.Paste
            Next
        wb.Close savechanges:=False
        Filename = Dir
    Loop
    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Cells(erow, 1).Select

End Sub
cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
Fane
  • 27
  • 9
  • You forgot to declare *counter* variable. – Error 1004 Mar 15 '19 at 14:35
  • 1
    Indenting your code will do wonders to prevent those kinds of errors. See my edit to your question, makes the missing `Next` more obvious – cybernetic.nomad Mar 15 '19 at 14:36
  • 1
    You may also want to read up on [avoiding activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – cybernetic.nomad Mar 15 '19 at 14:37
  • 1
    I've got one word for you: indentation, indentation, indentation. – SJR Mar 15 '19 at 14:41
  • 1
    @SJR: technically speaking, that's 3 words :) – Zac Mar 15 '19 at 14:45
  • 3
    `Option Explicit` is your friend – Zac Mar 15 '19 at 14:45
  • You have no closure for the second and third do's. Missing out the closure for a do loop gives a misleading missing next error message. i.e. I would expect to see three Loop statement, 1 to match each do. That's why it called a do Loop. – freeflow Mar 15 '19 at 14:46
  • 1
    @Zac - counting is not my strong point. If I were to use 3 different words I would add your two too. – SJR Mar 15 '19 at 14:58
  • 1
    In addition to using "option explicit" and properly indenting your code there's another small and easy trick you can apply: Name your "for..next" loops. If the index your "for..next" uses is "ws", write "next ws" at the end of your loop instead of a plain "next". I will help you to keep track of those loops. – Antonio Rodulfo Mar 15 '19 at 15:58
  • Hi All! Thank you all for your support! Sorry, but I am a novice at VBA. I tried to put for each Do (2'nd and 3'rd) > Next, but now I have error on line "End Sub: Do without Loop" – Fane Mar 18 '19 at 06:11

0 Answers0