CODEWITHSUNDEEP
excelvba
OviSele
OviSele

Reputation: 33

VBA to fill an existing value down until last row

I already have a working macro that is transposing 5 rows into columns and append them after the last column.

The problem is that I need a long format so basically I want to automate the filling up, with the same data, until the end of data (basically the last row). I have 600 files and all these files have a different number of rows.

Sub LoopFiles()
    Dim xFd As FileDialog
    Dim xFdItem As Variant
    Dim xFileName As String
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show = -1 Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        xFileName = Dir(xFdItem & "*.xls*")
        Do While xFileName <> ""
            With Workbooks.Open(xFdItem & xFileName)
                'your code here
                Range("A2:B6").Select
                Selection.Copy
                Range("I10").Select
                Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                    False, Transpose:=True
                Rows("1:8").Select
                Application.CutCopyMode = False
                Selection.Delete Shift:=xlUp
                With Sheets("Calculated Saccades")
                    .Range("I3").AutoFill .Range("I4:I" & .Cells(.Rows.Count, "A").End(xlUp).Row)
                End With
                ActiveWorkbook.Save
                ActiveWorkbook.Close
            End With
            xFileName = Dir
        Loop
    End If
End Sub

The With Sheets is returning an error. Any ideas? Thank you so very much!

Upvotes: 0

Views: 733

Answers (1)

aucuparia
aucuparia

Reputation: 2051

You're already inside a With...End With block (With Workbooks.Open(xFdItem & xFileName) - this loops through workbooks if you select more than one in the File Open dialog box); you can't nest these.

You can simply replace

    With Sheets("Calculated Saccades")
  .Range("I3").AutoFill .Range("I4:I" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With

with:

    Sheets("Calculated Saccades").Range("I3").AutoFill
    Sheets("Calculated Saccades").Range("I4:I" & .Cells(.Rows.Count, "A").End(xlUp).Row)

You should also read How to avoid using Select in Excel VBA.

Upvotes: 1

Related Questions