Combining Information - VBA Code

John_Gil

New Member
Joined
Jun 3, 2020
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
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):

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

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

New Member
Joined
Jun 3, 2020
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA Code:
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

John_Gil

New Member
Joined
Jun 3, 2020
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
The information should be pasted as below:

1ProductDepotFactTypeUnitDateValueMarket
2RPP372DUBLINCustomer AmendmentsB01/01/2018-54DEFAULT
3RTB127DUBLINCustomer AmendmentsB02/01/2018-8DEFAULT
4RPP372DUBLINCustomer AmendmentsB02/01/2018-5DEFAULT
5RTB566BELFASTCustomer AmendmentsB02/01/2018-13DEFAULT
6RTB658BELFASTCustomer AmendmentsB02/01/2018-24DEFAULT

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

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,512
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,512
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jun 3, 2020
Messages
42
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 7, 2012
Messages
12,512
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,512
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,816
Messages
5,542,656
Members
410,566
Latest member
Jonniehoffman
Top