Feb 25 2021 08:11 AM
Hello,
I've look through all the suggestions but couldn't find any suggestion or solution for my problem.
I have a Excel Workbook with two sheets and in the first one it contains a table that it is populated by an External Link via SQL that loads the desired information.
In the second sheet I have another table that acts as a dashboard where it has only the columns of the info that really matters.
My goal is based on the order number, list all the rows (dynamically) from he original table into each column.
If I change the order number, the info would change accordingly the information on the original table
PS: I'm using Office 2010.
Feb 25 2021 08:54 AM
The following column headers on the Dashboard sheet do not occur on the Montblanc sheet, so code to copy data will fail.
N° of client order
Position
Delivery date (Exit Bradco)
Qty
Price
Currency
Feb 25 2021 10:09 AM - edited Feb 25 2021 10:10 AM
That Excel is a dummy file. It's what I've been testing on.
If the columns match on either side it is possible to do?
Can you tell \ hint me on how to do it?
Thanks
Feb 25 2021 11:06 AM
Make sure that each of the column headers in D1:Q1 on the Dashboard sheet exactly matches a column header in row 1 of the Montblanc sheet.
And the column header in B1 must also match the column on which you want to filter exactly. For example Nº of intern order.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B2"), Target) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error GoTo ExitHere
Worksheets("Montblanc").UsedRange.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("B1:B2"), _
CopyToRange:=Range("D1:Q1")
ExitHere:
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open the workbook.