Combining Information - VBA Code

[John_Gil]

Hello Excel masters!

I need to develop a VBA code that brings information from a different file (3 different tabs) and combines everything into one final file.

The final file looks like the table below. We have 7 columns (column 3, 4 and 7 have always the same value):

CustomerAmendments.csv

Shared with Dropbox

www.dropbox.com

The file from where the information is coming has the previous view:

Combined amendments.xlsm

Shared with Dropbox

www.dropbox.com

Steps of the macro:
1- Filter in tabs "NI" and "ROI" column R by "C" and copy the information from columns C, D, I and P;
2- Filter in tab "OCADO" column Q by "C" and copy the information from columns C, D, I and o;
3- Paste information on the final file according with by column order shown in the final file.


I would really appreciate your help guys!

Thank you,
John

 

[John_Gil]

Apologies for the confusing. Is not producing any result - no information is copied.

The macro opens the file but it does not filter the information from those 3 tabs nether the information is copied.

Below the code I'm using at the moment:

Sub CustomerAmends()
Dim sh As Worksheet, ary As Variant, i As Long
Dim wb As Workbook
Set wb = Workbooks.Open("X:\Planning\production plans\production plans\Order Amendments\Combined amendments.xlsm")
Set sh = wb.Sheets(1)
ary = Array("NI", "ROI", "OCADO")
    For i = LBound(ary) To UBound(ary)
        With Sheets(ary(i))
            If i <> UBound(ary) Then
                .UsedRange.AutoFilter 18, "C"
            Else
                .UsedRange.AutoFilter 17, "C"
            End If
            .Range("C2", .Cells(Rows.Count, 3).End(xlUp)).Copy sh.Cells(Rows.Count, 5).End(xlUp)(2)
            .Range("D2", .Cells(Rows.Count, 4).End(xlUp)).Copy sh.Cells(Rows.Count, 1).End(xlUp)(2)
            .Range("I2", .Cells(Rows.Count, 9).End(xlUp)).Copy sh.Cells(Rows.Count, 6).End(xlUp)(2)
                If i <> UBound(ary) Then
                    .Range("P2", .Cells(Rows.Count, 16).End(xlUp)).Copy sh.Cells(Rows.Count, 2).End(xlUp)(2)
                Else
                    .Range("O2", .Cells(Rows.Count, 15).End(xlUp)).Copy sh.Cells(Rows.Count, 2).End(xlUp)(2)
                End If
            .AutoFilterMode = False
        End With
    Next
End Sub

Regarding you second question, the code is on module 1 and the destination file is called "Customer Amends".

Once again thank you,
John

 

[John_Gil]

The information should be pasted as below:

1	Product	Depot	FactType	Unit	Date	Value	Market
2	RPP372	DUBLIN	Customer Amendments	B	01/01/2018	-54	DEFAULT
3	RTB127	DUBLIN	Customer Amendments	B	02/01/2018	-8	DEFAULT
4	RPP372	DUBLIN	Customer Amendments	B	02/01/2018	-5	DEFAULT
5	RTB566	BELFAST	Customer Amendments	B	02/01/2018	-13	DEFAULT
6	RTB658	BELFAST	Customer Amendments	B	02/01/2018	-24	DEFAULT

But for the code I believe it should be doing it :/

 

[JLGWhiz]

Well, the destination file that the code uses is called 'Combined Abmendments' so if you look on sheet one of that file you might find your copied data.

 

[JLGWhiz]

You should always be clear about how many workbooks you are dealing with, their names and the sheets within each workbook, by workbook, that you are dealing with. We cannot see your firles nor do we know what your data is for or how you use it. All we provide is a means to manipulate the data and to do that we need to know exactly where it resides, the type of data, the sheet layout in terms of rows and columns for each sheet involved and peculiarities such as formulas and merged cells if pertinent. The host workbook for the code should be specified and it should also be specified which workbooks will be open at run time and which will not. If workbooks are in different directories than the host workbook, their path should be provided, or a range that lists the paths should be provided. This type information allows for quick and efficient responses on the forum.

 

[John_Gil]

I understand and agree but I mentioned that on my first comment specifying that there were two files involved and from which one the information was coming. The only thing that I did not mentioned is the name of the tabs from the file where the information is coming. No merged cells and no formulas are present. The Host file for the macro and importing the information is called Customer Amends.

Also I checked and the information is not being store on "Combined Amendments" as well :/

Please let me know if I can provide you any other information. I shared the files on my first comment as well.

 

[JLGWhiz]

No, I am completely baffled. The code worked as expected in a test setup, but of course I used different path and file names. If you open the vb editor and use the F8 key to step through the code, perhaps you can spot where it might be skipping the copy/paste action or not addressing the correct workbook. At this point, I would not know how to change the code to make it work for you.

 

[JLGWhiz]

One thing I just noticed about the OCADO sheet is that column Q has no values of "C" in it, so there would be nothing to copy for that sheet.