Opening .XLSX files and copying/pasting data

Question

I am trying to create a macro that loops through all the non-txt files in a folder, open them, copy a selection from the opened file, and then paste it to a specific sheet in the file with the macro (depending on which file is being copied). I've got the first two parts working, but I'm not able to get the copy part to work. It keeps copying the file that should be the paste file. Any idea what I'm doing wrong?

Private Sub CommandButton1_Click()

Dim Path As String
Dim File As String
Dim PasteFile As String
Dim Month As String
Dim FY As String

Month = "feb"
FY = "18"
PasteFile = ThisWorkbook.Name

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Path = "[file path]"
file = Dir(Path)
Do While file <> "*.txt" Or file <> ""

    If file = "[file name]" & Month & FY & ".xlsx" Then
    Workbooks.Open Path & file
    Workbooks(file).Worksheets("Sheet1").Activate
    Range(Cells(1, 1), Cells(5, 5)).Copy
    Workbooks(myFile).Worksheets("Sheet1").Activate
    Cells(10, 3).PasteSpecial xlPasteValues

    End If
File = Dir()
Loop
End Sub

Check this out: https://stackoverflow.com/questions/19351832/copy-from-one-workbook-and-paste-into-another — R2B Boondocks, Apr 25 '18 at 14:26

score 0 · Accepted Answer · answered Apr 25 '18 at 14:43

Avoid using .Activate and .Select

How to Avoid the Select Method in VBA & Why

Private Sub CommandButton1_Click()

Dim Path As String
Dim File As String
Dim PasteFile As String
Dim Month As String
Dim FY As String

Month = "feb"
FY = "18"
PasteFile = ThisWorkbook.Name

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Path = "[file path]"
File = Dir(Path)
Do While File <> "*.txt" Or File <> ""

    If File = "[file name]" & Month & FY & ".xlsx" Then

    Workbooks.Open Path & File

    With Workbooks(File).Worksheets("Sheet1")
    .Range(.Cells(1, 1), .Cells(5, 5)).Copy
    End With

    With Workbooks(myFile).Worksheets("Sheet1")
    .Cells(10, 3).PasteSpecial xlPasteValues
    End With

    End If

File = Dir()
Loop

End Sub

Opening .XLSX files and copying/pasting data

1 Answers1