Copy Data from 5 files to one file and copy it to the next spare row using VBA

[Tom90]

I can anyone help me, I am looking for a Macro that will open up files from My Documents and copy and paste on of the Sheets (Master) into a file I need this to happen for 5 files but when they are being copied to the new file I need then to be pated in the next available row where the previous data finished.
I have got some VBA from a previous worksheet and I am trying to adapted it but not having much success
I can get it to copy the first file over but cannot get it to close it so i can nove onto the next file also not to sur how to find the next clear row so it can be pasted into

Any help would be great

Thanks Tom90
[drc = Range("D" & Application.Rows.Count).End(xlUp).Row]
[For i = 1 To drc]
[wb_pth = Cells(i, 4).Value]
[Workbooks.Open (wb_pth)]
[wb = ActiveWorkbook.Name]

[Windows(wb).Activate]

[Sheets("Master").Select
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
Columns("A:AT").Hidden = False
Range("A2:AP30000").Copy
Windows("WorkStack Exchange Drop B.xlsm").Activate
Sheets("Data Drop").Select
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
Columns("A:AT").Hidden = False
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False]

[Range("A1").Select
Sheets("Exchange").Select
ActiveWorkbook.Save]

[Workbooks.Open (wb_pth)
Sheets("Sheet1").Select
ActiveWorkbook.Save
ActiveWindow.Close]


Next i

 

[kchiba]

Hi Tom,

Please upload the file it would make it easier to solve the problem as I am not sure when you are working whit any particular workbook

kanti

 

[Tom90]

Hi Kanti,
Thanks for you interest, the file that I have attached is the one that I am trying to download all the data from 5 other file I have shown this by the five colures in the file each colure was taken from a different file they all have the same name, Workbook_HT, Workbook_GS, they all start with Workbook_ with a different two letters at the end, I also need the VBA to recognise the next free row and paste the data for the next file into and so on until I have all the Workbook_ Data in the file “Drop File which is the one that should also contain the macro
Thanks Tom

 

[kchiba]

Hi Tom,

Please upload the file with the Macro, it should be a .xlxm file

 

[Tom90]

Hi Kchiba,

As repuested, Tom

 

[SirJB7]

Hi, Tom90!

Give a look at the uploaded file. This is the code:

Option Explicit

Sub AllTogetherNow()
    ' constants
    Const ksPrefix = "Drop File "
    Const ksInput = "@ 1 2 3 4 5"
    Const ksSuffix = ".xlsx"
    ' declarations
    Dim vInputFile As Variant
    Dim I As Long
    ' start
    vInputFile = Split(ksInput)
    For I = 1 To UBound(vInputFile)
        vInputFile(I) = ksPrefix & vInputFile(I) & ksSuffix
    Next I
    ' process
    For I = 1 To UBound(vInputFile)
        Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & vInputFile(I)
        With Worksheets(1)
            Range(.Rows(2), .Rows(2).End(xlDown)).Copy _
                ThisWorkbook.Worksheets(1).[a1].End(xlDown).End(xlDown).End(xlUp).Offset(1, 0)
        End With
        ActiveWorkbook.Close False
    Next I
    'end
    [a1].Select
    Beep
End Sub

It works as long as you define properly the filenames for the 5 files (in the example Drop file N.xlsx); otherwise assign them manually, replacing the Split and For...Next loop:
vInputFile(1)="..."


Just advise if any issue.

Regards!

 

[Tom90]

Hi Sir JB7,

See where you have "Const ksPrefix =" is this were i put the file name of the 5 files that I am downloading into this file
Const ksPrefix = "Drop File "

See where "Const ksInput = "@ 1 2 3 4 5" is there were I put the file numbers so that "Const ksPrefix =" & Const ksInput = "@ 1 2 3 4 5" match to make up the loop for the 5 files
Const ksInput = "@ 1 2 3 4 5"

Tom

 

[SirJB7]

Hi, Tom90!
In the start section you should load / build the vInputFile array, either as I did (with Split and For...Next loop) or with a direct assignment as:
vInputFile(1)="Drop File 1.xlsx"
vInputFile(2)="Drop File 2.xlsx"
vInputFile(3)="Drop File 3.xlsx"
vInputFile(4)="Drop File 4.xlsx"
vInputFile(5)="Drop File 5.xlsx"
... or the actual names that you have.
Then the rest should work fine.
Regards!

 

[Smallman]

This method means you have to hard code the file names into the back end. Change is constant and if the file names change then the procedure falls over. I would suggest you put your 5 files into a dedicated folder. Place them there every month, week, day however open you want to run the file. Then just run this procedure. It is low on looping and will bring all of the files in your dedicated folder no matter the name when ever you call on the procedure. You just need to remember that all files in this folder are brought back - this is the process.

Option Explicit
Sub OpenImp()
    Const sPath = "C:\Test\" 'Change to suit
    Dim sFil As String
    Dim owb As Workbook
    Dim ws As Worksheet
 
    Set ws = Sheet2 'This is the Sheet Name from your Drop File (assume it is still Sheet2)
    sFil = Dir(sPath & "*.xl*") 'Flexible enough to handle all XL file types

    Do While sFil <> "" 'Only Copies Cols A to P based on Drop File
        Set owb = Workbooks.Open(sPath & sFil)
        Range("A2", Range("P" & Rows.Count).End(xlUp)).Copy ws.Range("A" & Rows.Count).End(xlUp)(2)
        owb.Close False 'Close no save
        sFil = Dir
    Loop
End Sub

You will just need to decide where you are to save the files. I have created a file Called C/Test. Don't forget the back slash if you used this procedure.

Take care

Smallman

 

[Tom90]

Hi Guys,

Sorry for the late reply but I am from Scotland so we celebrate the New Year for a few days, I have just managed to get myself on line and you have solved my problem and it is now working, so once again thanks for all you help and a Good New Year to you all.

Tom

 

[SirJB7]

Hi, Tom90!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!