Moving columns in a table based on column headers

[Siddhart Nagwekar]

Hi All,

I create a report where in I need to rearrange the columns in a particular manner as required by the client. Could you help me with a macro which will will work as per following criteria
Column headers start from A1 onwards (the last column header might vary depending on the creator, I get the report from a different team)
Rows start from A2 and the number of rows of data might vary
For changing the order of columns, the macro should look for a column with the header "Close Date", cut it and insert is before a column with header "Bookings". Then, the macro should look for a column with the header "Description", cut it and insert is before a column with header "Selling BU". The macro needs to then cut two adjacent columns namely "Sector" and "Sub-Area" and insert it before the column with header "Total Bookings"

There will be several columns moved, if you give me the code for the above, I can modify it so that further column movements can be accommodated.


Any help will be appreciated.

 

[Luke M]

Instead of cutting/pasting, you could do an Advanced Filter with no Criteria. Adv.Filter has the nicety of letting you re-arrange column headers (even have fewer than data source). See the attached for a nice example.

 

[Siddhart Nagwekar]

Thanks Luke M. I apologies to you but I didn't understand your example. How dpes the macro determine which columns to move and the order of pasting. And yes, in your example, the copy and the paste ranges are different, while I both to be the same i.e. the final range address will remain the same although the columns would have shuffled.

 

[Luke M]

Hi Siddhart.

In the macro, I've manually type the ranges of where the ranges our. You would change these to match your specific workbook. (see comment "setup our ranges"


The order of pasting is determined inherently by the AdvFilter. It just looks at the column headers, and matches things up. No need for the macro to have to work to figure it out.

 

[Siddhart Nagwekar]

Thanks Luke. This works for me perfectly.

 

[Luke M]

1. Yes, that's exactly right.

2. They can be in different locations. Two things to pay attention to
a) Destination worksheet needs to be the active sheet
b) Define ranges fully, with Parent objects. e.g.:
Workbooks("Source Book.xls").Worksheets("Sheet1").Range("A1:G100")

3. Having extra columns won't be a problem in terms of the filter, as any columns not listed in destination will just be ignored. Only trick would be making sure the sourceRange is defined correctly, to gather all the data you need.

EDIT: I see now that you figured it out, awesome!

 

[Siddhart Nagwekar]

Hi Luke M,

There is a slight glitch. As long as the data was on the same sheet, it worked fine but as soon as I try to copy data across workbooks I get one of the following error messages

This one, if I keep the Source worksheet as active worksheet

and this one if I keep the destination worksheet as active (I tried both as I was unable to debug)

Since my data was in two different workbooks (both open at the time of running the macro), I modified the macro from your attached workbook i.e. advfilter as per the instructions in your previous post as follows
With ActiveSheet

'Dynamic last row, incase data grows/shrinks
  lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   
  'Setup our ranges
  Set sourceRange = Workbooks("Outlook.xlsx").Worksheets("Full PipeLine").Range("A1:EZ" & lastRow)
  Set destRange = Workbooks("Thursday_Working.xlsx").Worksheets("Full PipeLine").Range("A1:DS1")
   
  'Copy the data
  sourceRange.AdvancedFilter xlFilterCopy, , destRange
End With

What am I doing wrong here. Please let me know as soon as possible as I am at a very crucial juncture and need to complete this as soon as fast as possible.

Thanks so much for your invaluable help

 

[Luke M]

Code looks good, and were on the right path w/ Source workbook being active. The error message tells us that either:

• Header names in one of the two places are duplicated
• One or more of the Header names in Dest are not found in Source (e.g., if Source uses label "First Name", Dest can't use "F Name".
• There is a blank cell in header row range

Can you check the header labels again, make sure none of the above are happening?

 

[Siddhart Nagwekar]

Hi Luke M,

You were right. There were duplicates in both the source and the destination workbooks. I have eliminated these duplicates by including code to insert a decimal after the text in one of the duplicates. I thought that this would resolve the error, however, the '1004' error still persists. The code gets stuck (goes yellow) at the below mentioned line

sourceRange.AdvancedFilter xlFilterCopy, , destRange

before I run your code, I delete the previous data from the destination sheet (leaving the column headers for the advance filter macro). Is this causing the problem?

I am attaching the worksheets from the source and destination workbooks (just the single worksheets) so that you can investigate.

 

[Luke M]

Hmm, everything looks good. I just ran this test macro successfully.

Sub testAdv()
'Macro is stored in Source workbook, source workbook is active
Dim sourceRange As Range, destRange As Range
Set sourceRange = ThisWorkbook.Worksheets("Full PipeLine").Range("A1:DS1161")
Set destRange = Workbooks("Destination.xlsx").Worksheets("Full PipeLine").Range("A1:DS1")

sourceRange.AdvancedFilter xlFilterCopy, , destRange
End Sub

Be careful when defining DestRange that you're not including columns DT:DW, as those are not in Source file. I'm guessing those have some type of formula maybe?

 

[Siddhart Nagwekar]

The macro is not stored in the source workbook. Its stored with the rest of my macros in a workbook called My Macros. That is the place from where I run the macro. Hence, I cannot use Thisworkbook.worksheets

 

[Luke M]

That's ok. You would replace the ThisWorkbook object then with the appropriate reference, like:
Workbooks("Source")
Sorry for the confusion.

 

[Siddhart Nagwekar]

Finally I got it right. The problem was in the line

lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

There is nothing in the before .cells and that is the reason the code wasn't working. Once I entered the path up to the worksheet, the code worked flawlessly.

Thanks a lot for the patience and your relentless effort without giving up. Your solution will play a major part of all the codes that I will write in the years to come!!