VBA - Copy and Paste varying rows in Closed Workbook

ABF

New Member
Joined
Oct 1, 2020
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,736
Try:
VBA Code:
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
 
  • Like
Reactions: ABF

ABF

New Member
Joined
Oct 1, 2020
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
Try:
VBA Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
9,736
You used
VBA Code:
Paste:=xlPasteFormats
This paste the formats only. Do you want to paste formats and cell content?
 
  • Like
Reactions: ABF

ABF

New Member
Joined
Oct 1, 2020
Messages
7
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

You used
VBA Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
9,736
Try:
VBA Code:
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
 
  • Like
Reactions: ABF

ABF

New Member
Joined
Oct 1, 2020
Messages
7
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Try:
VBA Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
9,736
Try:
VBA Code:
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
 
  • Like
Reactions: ABF

ABF

New Member
Joined
Oct 1, 2020
Messages
7
Office Version
  1. 2010
Platform
  1. Windows
Try:
VBA Code:
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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,016
Messages
5,545,514
Members
410,689
Latest member
ConfuzzledThomas
Top