VBA for UK Postcodes

**hannsg** · 08-05-2016, 07:06 AM

Hi,

I have a huge list of data that I need to split into separate postcode areas.

For example...

Phone Number - First Name - Last Name - Address - Postcode

I want to split each postcode area into a different file. BS1 BS2 BS3 BS4 and so on. However, some of the postcodes are in different formats so one might be BS1 1AA and another could be B1 1AA. When I select the postcodes to move to another sheet the other data on the same row has to move as well to stay relevant.

Is there any easier way for me to do this other than manually selecting each area then copy and pasting?

Thanks.

**José Augusto** · 08-05-2016, 07:15 AM

Hi
Can you send a sample of your data?

**AliGW** · 08-05-2016, 07:21 AM

Extracting the start of a UK postcode into a helper column or as part of a formula is not a problem. This will do it:

=TRIM(LEFT(A1,FIND(" ",A1)))

However, I think you need a VBA solution to get the data moved.

**Pete_UK** · 08-05-2016, 07:26 AM

It might be easier to sort the data by postcode, and then you can cut/paste into your other sheets.

Hope this helps.

Pete

**hannsg** · 08-05-2016, 08:00 AM

José Augusto
Re: UK Postcodes

Hi
Can you send a sample of your data?

Phone First Name Last Name Add1 Add2 Add3 Postcode
1234567891 J Smith 100 Scott Street Bristol BS15 4RT
2345678910 S Thomson 114 Stable Drive Bristol BS4 9QV
3456789101 M Jackson 17 Maple Avenue Bristol BS19 5HU

So I want a separate file for each of those areas BS15 BS4 BS19

Pete_UK
Re: UK Postcodes

It might be easier to sort the data by postcode, and then you can cut/paste into your other sheets.

Hope this helps.

Pete

I have it sorted by postcode already but copying and pasting each postcode sector is going to take way too long.

**AliGW** · 08-05-2016, 08:02 AM

As I said before, you are likely going to need a macro/VBA solution for this. Try editing the title of your opening post to add VBA into the title, which will hopefully attract the attention of the coders here.

**protonLeah** · 08-05-2016, 03:22 PM

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

Click Advanced next to Quick Post button at the bottom right of the editor box.
Scroll down until you see "Manage Attachments",
Click the "Choose" button at the upper left (upload from your computer).
Select your file, click "open", click "upload"
Once the upload is completed the file name will appear below the input boxes in this window.
Close the Attachment Manager window.
Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

**Pete_UK** · 08-05-2016, 05:04 PM

Here's a thread that I contributed to a few years ago for a poster who was asking for a search engine:

http://www.excelforum.com/excel-gene...readsheet.html

and it was referred to again in another thread earlier this year:

http://www.excelforum.com/excel-gene...ine-sheet.html

It's not exactly the same as your problem, but it does allow you to type a partial postcode in a cell and to display all the records that match in a separate sheet (actually, from a number of different sheets, although you could just specify one sheet). This might be easier/better than having multiple sheets for each postcode area.

Hope this helps.

Pete

**Richard Buttrey** · 08-05-2016, 05:10 PM

Hi,

Once you've split your postcode into the Incoming office and the outgoing location then why not use a Pivot Table to analyse your data.

**protonLeah** · 08-10-2016, 12:46 AM

Please Login or Register  to view this content.

**hannsg** · 08-10-2016, 11:05 PM

Thanks for taking the time to write something up for me.

I am completely new to this kind of thing...

I hav attached this code to a button using visual basic in the developer setting of excel.

When I click the button nothing happens.

**protonLeah** · 08-11-2016, 01:08 AM

Post the workbook. I was only guessing at what you are working with.
(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

Click Advanced next to Quick Post button at the bottom right of the editor box.
Scroll down until you see "Manage Attachments",
Click the "Choose" button at the upper left (upload from your computer).
Select your file, click "open", click "upload"
Once the upload is completed the file name will appear below the input boxes in this window.
Close the Attachment Manager window.
Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

**hannsg** · 08-18-2016, 08:23 AM

What you have done is actually exactly what I'm looking for only my data is in separate columns.

Here is a sample workbook. Sorry it has taken me so long to get back to you.

Please bare in mind my actual workbook may have up to 500,000 rows in it.

Thanks

**José Augusto** · 08-18-2016, 09:11 AM

Hi
I think you not need VBA
Select column with your data
Go to Data separator and choice Text to Columns (with comma separator)

**hannsg** · 08-18-2016, 10:29 AM

Hi José,

The VBA by ProtonLeah is almost exactly what I need.

I definitely can not do what I need by doing what you have suggested.

**hannsg** · 09-04-2016, 12:20 PM

Really need a solution to this. Would greatly appreciate it.

**mike7952** · 09-04-2016, 01:41 PM

Maybe this

Please Login or Register  to view this content.

**hannsg** · 09-04-2016, 02:19 PM

Thanks for the reply mike.

This is almost what i am looking for... The only issue is that, I only want it sorted on the first section of the postcode.

For example:

1224202407 G Peters 12 Morrison Dv Aberdeen AB10 7HD

gets sorted into a worksheet called AB10 and any other postcodes that start with AB10 also go into that sheet. Whether that be AB10 7HD, AB10 8LA, AB10 9PK.

I appreciate your time helping me.

**mike7952** · 09-04-2016, 02:51 PM

this what you want?

Please Login or Register  to view this content.

**hannsg** · 09-04-2016, 03:45 PM

Thanks mike, that's what I'm looking for but...

When I am trying it with a lot more rows I am getting error subscript out of range. Do you know why this is?

**mike7952** · 09-04-2016, 03:55 PM

What is highlighted when the code errors?

**hannsg** · 09-04-2016, 04:16 PM

.Item(sPostalCode) = w
Next

The cursor sits at the end of "Next"

**mike7952** · 09-04-2016, 04:43 PM

What is the value of cell what is the value of sPostalCode

**sanram** · 09-04-2016, 05:24 PM

Originally Posted by hannsg

Thanks mike, that's what I'm looking for but...

When I am trying it with a lot more rows I am getting error subscript out of range. Do you know why this is?

You are getting this error because you are exceeding the maximum number of sheets in a single workbook. Excel support maximum 255 sheets in a single workbook as far as I know.

If you just want to split the postcodes on attached sample in 2 different column then you can do this easily with those formulas. But actually I am not clear about requirements.
On I2 write this:

Please Login or Register  to view this content.

On J2 write this:

Please Login or Register  to view this content.

Now copy them down.

**mike7952** · 09-04-2016, 05:33 PM

@sanram

That is not his problem. The line of code that has an error is not creating worksheets yet.

**sanram** · 09-04-2016, 06:40 PM

@mike7952
Actually I couldn't find any error in your code. But hannsg has stated before that his original sheet contains 500000 rows. That's why I have told this. But may be you are right, because I am not so good with array in VBA.

**hannsg** · 09-04-2016, 07:02 PM

OK So it only works with up to around 800 rows it won't do much more than that.

**hannsg** · 09-04-2016, 07:03 PM

@mike7952

I have PM'd you but I don't know if my PM's are working as they are not showing in sent box. Let me know if you get it.

**hannsg** · 09-04-2016, 07:04 PM

Originally Posted by sanram

@mike7952
Actually I couldn't find any error in your code. But hannsg has stated before that his original sheet contains 500000 rows. That's why I have told this. But may be you are right, because I am not so good with array in VBA.

Hi Sanram,

I definitely won't be using more than 255 sheets. Most I will ever use is about 80.

**sanram** · 09-04-2016, 07:10 PM

As I am not good in array I have created a normal solution for you. Try this:

Please Login or Register  to view this content.

**hannsg** · 09-04-2016, 07:52 PM

again, It seems to work fine for the sample sheet but when I add more rows nothing seems to work.

**hannsg** · 09-04-2016, 07:56 PM

Sanram, your solution sorts the first postcode AB1 then an error occurs says subscript out of range.

**mike7952** · 09-04-2016, 08:09 PM

Problem was PostCode being empty. Code skips that row now

Please Login or Register  to view this content.

**hannsg** · 09-04-2016, 08:22 PM

Originally Posted by mike7952

Problem was PostCode being empty. Code skips that row now

Please Login or Register  to view this content.

You are an absolute legend mike!

Thank you so much, just saved me days!

**mike7952** · 09-04-2016, 08:32 PM

Your welcome, please mark thread closed.

**sanram** · 09-05-2016, 01:37 AM

Originally Posted by hannsg

Sanram, your solution sorts the first postcode AB1 then an error occurs says subscript out of range.

VBA for UK Postcodes

Re: UK Postcodes

Re: UK Postcodes

Re: UK Postcodes

Re: UK Postcodes

Re: UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Re: VBA for UK Postcodes

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Vlookup Postcodes

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions