VBA - Copy and Paste varying rows in Closed Workbook

[ABF]

I have a file that I pull weekly (Site x) and I need to copy the data from this sheet to the bottom of the other file called "Site x Compiled". I have the code below, but the part of copy and paste is not working.
I'm still learning how VBA works, so can someone help me accomplish this?

````
Sub Compiling()

Dim InputFile As Workbook
Dim OutputFile As Workbook
Dim Inputpath As String
Dim Outputpath As String


'OKAY Set path for Input & Output
fileInputpath = "C:\Users\Desktop\Workbooks\"
Outputpath = "C:\Users\Desktop\Workbooks\"

'OKAY Open both workbooks first:
Set InputFile = Workbooks.Open(Inputpath & "C:\Users\Desktop\Workbooks\Site x.xlsx")
Set OutputFile = Workbooks.Open(Outputpath & "Site x Compiled.xlsx")

'NOT RUNNING PROPERLY - Now, copy what you want from InputFile:
InputFile.Sheets("Sheet1").Activate
InputFile.Sheets("Sheet1").Range("A2", Range("A2").End(xlDown).End(xlToRight)).copy

'NOT RUNNING PROPERLY - Now, paste to OutputFile worksheet:
OutputFile.Sheets("Sheet1").Activate
OutputFile.Sheets("Sheet1").Range("A2", Range("A2").End(xlDown)).Offset(1).PasteSpecial Paste:=xlPasteFormats

'OKAY Close InputFile & OutputFile:
Application.DisplayAlerts = False
InputFile.Close
OutputFile.Close savechanges:=True


End Sub

 

[mumps]

Try:

Sub Compiling()
    Application.ScreenUpdating = False
    Dim InputFile As Workbook, OutputFile As Workbook, Inputpath As String, Outputpath As String
    fileInputpath = "C:\Users\Desktop\Workbooks\"
    Outputpath = "C:\Users\Desktop\Workbooks\"
    Set InputFile = Workbooks.Open(Inputpath & "Site x.xlsx")
    Set OutputFile = Workbooks.Open(Outputpath & "Site x Compiled.xlsx")
    With InputFile.Sheets("Sheet1")
        .Range("A2", .Range("A2").End(xlDown).End(xlToRight)).Copy
        OutputFile.Sheets("Sheet1").Range("A2", OutputFile.Sheets("Sheet1").Range("A2").End(xlDown)).Offset(1).PasteSpecial Paste:=xlPasteFormats
    End With
    Application.DisplayAlerts = False
    InputFile.Close False
    OutputFile.Close True
    Application.ScreenUpdating = True
End Sub

 

[ABF]

Try:

Sub Compiling()
    Application.ScreenUpdating = False
    Dim InputFile As Workbook, OutputFile As Workbook, Inputpath As String, Outputpath As String
    fileInputpath = "C:\Users\Desktop\Workbooks\"
    Outputpath = "C:\Users\Desktop\Workbooks\"
    Set InputFile = Workbooks.Open(Inputpath & "Site x.xlsx")
    Set OutputFile = Workbooks.Open(Outputpath & "Site x Compiled.xlsx")
    With InputFile.Sheets("Sheet1")
        .Range("A2", .Range("A2").End(xlDown).End(xlToRight)).Copy
        OutputFile.Sheets("Sheet1").Range("A2", OutputFile.Sheets("Sheet1").Range("A2").End(xlDown)).Offset(1).PasteSpecial Paste:=xlPasteFormats
    End With
    Application.DisplayAlerts = False
    InputFile.Close False
    OutputFile.Close True
    Application.ScreenUpdating = True
End Sub

it still not pasting the data....

 

[mumps]

You used

Paste:=xlPasteFormats

This paste the formats only. Do you want to paste formats and cell content?

 

[ABF]

You used

Paste:=xlPasteFormats

This paste the formats only. Do you want to paste formats and cell content?

I got it, i changed to xlPasteValues now, but the data is pasting over the old data, i would like to paste at the bottom

 

[mumps]

Try:

Sub Compiling()
    Application.ScreenUpdating = False
    Dim InputFile As Workbook, OutputFile As Workbook, Inputpath As String, Outputpath As String
    fileInputpath = "C:\Users\Desktop\Workbooks\"
    Outputpath = "C:\Users\Desktop\Workbooks\"
    Set InputFile = Workbooks.Open(Inputpath & "Site x.xlsx")
    Set OutputFile = Workbooks.Open(Outputpath & "Site x Compiled.xlsx")
    With InputFile.Sheets("Sheet1")
        .Range("A2", .Range("A2").End(xlDown).End(xlToRight)).Copy OutputFile.Sheets("Sheet1").Cells(OutputFile.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Offset(1)
    End With
    Application.DisplayAlerts = False
    InputFile.Close False
    OutputFile.Close True
    Application.ScreenUpdating = True
End Sub

 

[ABF]

Try:

Sub Compiling()
    Application.ScreenUpdating = False
    Dim InputFile As Workbook, OutputFile As Workbook, Inputpath As String, Outputpath As String
    fileInputpath = "C:\Users\Desktop\Workbooks\"
    Outputpath = "C:\Users\Desktop\Workbooks\"
    Set InputFile = Workbooks.Open(Inputpath & "Site x.xlsx")
    Set OutputFile = Workbooks.Open(Outputpath & "Site x Compiled.xlsx")
    With InputFile.Sheets("Sheet1")
        .Range("A2", .Range("A2").End(xlDown).End(xlToRight)).Copy OutputFile.Sheets("Sheet1").Cells(OutputFile.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Offset(1)
    End With
    Application.DisplayAlerts = False
    InputFile.Close False
    OutputFile.Close True
    Application.ScreenUpdating = True
End Sub

thank you so much for your help, you are almost there! the only thing it that is pasting only from column A to H and the data I'm copy is from column A to U, maybe is not working because there are some empty columns? but idk how to resolve...

 

[mumps]

Try:

Sub Compiling()
    Application.ScreenUpdating = False
    Dim InputFile As Workbook, OutputFile As Workbook, Inputpath As String, Outputpath As String, lRow As Long
    fileInputpath = "C:\Users\Desktop\Workbooks\"
    Outputpath = "C:\Users\Desktop\Workbooks\"
    Set InputFile = Workbooks.Open(Inputpath & "Site x.xlsx")
    Set OutputFile = Workbooks.Open(Outputpath & "Site x Compiled.xlsx")
    With InputFile.Sheets("Sheet1")
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("A2:U" & lRow).Copy OutputFile.Sheets("Sheet1").Cells(OutputFile.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Offset(1)
    End With
    Application.DisplayAlerts = False
    InputFile.Close False
    OutputFile.Close True
    Application.ScreenUpdating = True
End Sub

 

[ABF]

Try:

Sub Compiling()
    Application.ScreenUpdating = False
    Dim InputFile As Workbook, OutputFile As Workbook, Inputpath As String, Outputpath As String, lRow As Long
    fileInputpath = "C:\Users\Desktop\Workbooks\"
    Outputpath = "C:\Users\Desktop\Workbooks\"
    Set InputFile = Workbooks.Open(Inputpath & "Site x.xlsx")
    Set OutputFile = Workbooks.Open(Outputpath & "Site x Compiled.xlsx")
    With InputFile.Sheets("Sheet1")
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("A2:U" & lRow).Copy OutputFile.Sheets("Sheet1").Cells(OutputFile.Sheets("Sheet1").Rows.Count, "A").End(xlUp).Offset(1)
    End With
    Application.DisplayAlerts = False
    InputFile.Close False
    OutputFile.Close True
    Application.ScreenUpdating = True
End Sub

OMG you are THE MAN!!!! THANK YOU SOOOOO MUCH!!!

 

[mumps]

You are very welcome.