Need a macro for filtering and pasting data

[skhande2]

Hi,

I need a macro that filters sheet 'Routing' by column E that starts with digit 6. It then filters column F by those that either start with a space. (or ones that don't start with number - your choice) It then copies the results and pastes it in sheet 'Outside processing' starting from cell B3. I also need the pasted result to have double lined border around it.

I've attached the sheet as reference. I've manually entered the results but you may delete the results in Outside Processing sheet (except column A) and see if it replicates it. Thank you!

testing 2.xlsx

[ORoos]

Hi there,

Please see attached file with macro.

To filter column 'E' numbers by the first digit (6), we convert the numbers first to 'Text' (convert back to 'General' at the end).
To filter column 'F' if the first digit is a number, I used a 'helper' formula in column 'K'. If you have other data there, move to the right of the last used column. This is deleted again at the end.

Not sure what you mean with double-lined borders. They are normally just available at the bottom of the cell...

There are probably more elegant solutions, but the code below should work.

Please Login or Register  to view this content.

[skhande2]

If you look at routing sheet in the excel I uploaded you will see how there is a special border around the pasted range. I want that border when macro automatically pastes it.

[skhande2]

Actually, I solved the border thing but another issue I found is if there are any values in column K of sheeting routing, it gets deleted and all the columns to its right it shift 1 column to their left replacing column k after the macro is executed. Could you ensure this doesn't happen? Thanks!

[johnnyL]

@skhande2, try the following vba code, it is only set up to work with your sample data, but it should give you the jist. You would also need to apply the grid lines if you want them, but it sounds like you already have a way to handle that:

Please Login or Register  to view this content.

[ORoos]

Actually, I solved the border thing but another issue I found is if there are any values in column K of sheeting routing, it gets deleted and all the columns to its right it shift 1 column to their left replacing column k after the macro is executed. Could you ensure this doesn't happen? Thanks!

Replace:
Columns("K:K").Select
Selection.Delete Shift:=xlToLeft

With:
Columns("K:K").Clear

[skhande2]

Thanks for the help but its still messing up column K. (tried both of your solutions..) I've uploaded the file with the actual data. In hindsight, maybe I should have done it to begin with. Thanks!
Practice.xlsx

[ORoos]

The updated code incorporates some of the code from "johnnyL". Thanks for a simpler solution without helper column!

Yes, uploading a sample reflecting the actual date is making it a lot easier to find a solution.
I also noted about using column K as a helper column in my

I used a 'helper' formula in column 'K'. If you have other data there, move to the right of the last used column

.

Still not sure about the double boarder thing, but I think you worked that part out yourself.

[skhande2]

My computer won't allow me to execute implanted macro for security reasons. Could you paste the macro here? Thanks!

[ORoos]

Please Login or Register  to view this content.

[skhande2]

Could you ensure numbers in column F in sheet 'Outside Processing' are converted back into numbers from text? Much Appreciated!

[skhande2]

Also, this is my solution to double borders. I applied it to other macros and it works just fine but for some reason, to this macro, it won't end by the last row :

Please Login or Register  to view this content.

Rest of the macro is same, I just added the border part towards the end.

[johnnyL]

Here you go:

Please Login or Register  to view this content.

[ORoos]

This will change column F back to numbers, make the 'double line' dynamic based on how many rows of test you have.
Also added as a separate routine, called from the code, to reset any previous 'double-lines' you had in the output sheet before the double line boarder is applied.

Please Login or Register  to view this content.

[skhande2]

Thanks guys! So both of your macros error if there are no numbers to copy and paste.

-JohnnyL, your macro copy paste the header from routing into row 3 of Outside processing and removes its header.
-ORoos, your macro errors (which stops other macros after it from executing that I've added to it) and corrupts the values in column E at the table in the bottom of Routing.

ORoos, as you edit your macro, please edit the version of it I created below. Thanks!

You can test this on the sheet I pasted earlier by removing the rows that get copy and pasted but I've uploaded a file without those for your ease. Mucho Appreciato!

Please Login or Register  to view this content.

Welp.xlsx

[johnnyL]

@skhande2, I have come up with the following code that addresses the errors that you mentioned:

Please Login or Register  to view this content.

[skhande2]

I'm not sure what you mean. I've attached pictures that match up on both sheets columns E,F & G. When I executed your new macro, instead of running 0 results, it returns results it wasn't supposed to. (see the bottom sheet in both pics)
Attachment 834768
Attachment 834769

From the original post, the criteria was, "I need a macro that filters sheet 'Routing' by column E that starts with digit 6. It then filters column F by those that either start with a space. (or ones that don't start with number - your choice)"

As you can see in the pics, there are no cells in columns E that start with 6

[johnnyL]

I just edited my previous post.

[johnnyL]

I also just added an autofilter check to make sure we start with all autofilters removed.

[skhande2]

That works . Thanks!

[johnnyL]

Glad we could help.

[skhande2]

So..I'm having another issue. Not sure if you will experience it on your end but your macro works flawlessly when only 1 excel sheet is open. But when I've multiple excel sheets open, I get a dialog box, "There is already data here. Do you want to replace it?" Then, I notice nothing is copied over to Outside processing but its emptied out and multiple columns of routing (F,G and H) are wiped out.

[johnnyL]

I only see one potential issue in the code I submitted:

Change the second line of code from:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

Try that and see if it resolves the issue. If it doesn't can you further explain the issue you are having? You mentioned 'excel sheet', did you mean ' excel workbooks'?

[skhande2]

I meant workbooks, yes. Its not happening right now, when it happens again, I will test your correction.

[ORoos]

Adding my version back into the mix.
The updated macro will exit when no data gas been found to be copied.
The text2column bits will only convert and change back to data at the top, no longer the full column.

Let us know if that works.

Please Login or Register  to view this content.

[skhande2]

When I run this macro ORoos in a sheet where nothing gets copied, it converts numbers in column E in sheet 'Routing' into text. Could you have it convert back at the end? Thanks

[ORoos]

Try the code below and let us know if that works for you:

Please Login or Register  to view this content.

[skhande2]

That worked. Thanks! Hey, could you guys also please take a look at this post? Thanks!
https://www.excelforum.com/excel-pro...ml#post5845536

Appreciated!

[skhande2]

Actually, its erroring again when I'm combining it with the bulk of other macros, could you write this macro in a way so it doesn't call another routine? I think calling it is making it error. So just 1 Sub and end sub.

[ORoos]

Try:

Please Login or Register  to view this content.