Autfiltering, copy to another sheet then concatenate to a master sheet

[boris55]

Hi everyone,
I'm very new to VB and would like some pointers.
I have a series of spread sheets that are amended by others,
What I've been tasked to do is to bring together all of the common cells and display them on a master sheet.
Basically there are 4 sheets for each row, each with 54 rows and 27 possible locations and 19 rows per area and a total of 4 areas.
I have been searching the forums and found various bits of code the just about do the task but I feel it is a bit clumsy
I've been trying to work it with loops but keep flying off into oblivion,
Any help would be appreciated.

I have attached my working sheet, Autofilter MKII
The tabs are as follows
Row 1: Brings together the info from the active sheets, I've use Concatenate on the original sheets as the data is across 5 columns,
Work Area: I use to copy too after 'Autofilter' then Concatenate into 1 cell copy that to the side then repeat 27 times
Master: This is the desired 'Front Sheet' this just copies from 'Work Area'

[HaHoBe]

Hi, boris55,

sorry but I canīt see an attachment in this thread (and I think it would be helpfull indeed).

Ciao,
Holger

[boris55]

Hi, Holger,

I have managed to attach the file now,
Thanks for the update.

Boris

[HaHoBe]

Hi, Boris,

sorry but the code supplied wonīt find any values to copy from Sheets("Row 1") to Sheets("Work Area"), and I doubt it to be a good idea to use formulae in Sheets("Master") which refer only to that sheet all by themselves. In short:; as you have done some work to the sheet sfrom the other workbooks I canīt figure out how the original data looks like nor how it should be put into Sheets("Master") (okay, I have an idea about what it might turn out to be).

Ciao,
Holger

[boris55]

Hi Holger,

I am very sorry for wasting your time, I tried to smarten up the sheet to make it easier to understand, what I forgot to do was change all the references in the sheet.
I have done this now and re-attached it as 'Autofilter MIII.zip'

Regards
Boris

[HaHoBe]

Hi, Boris,

as your sample shows the formulas on Sheet Master leading toi that sheet - what about the code her? I assumed that the results need to be inserted into COlumn B on Master (made up by the constant clngCOLUMN):

Please Login or Register  to view this content.

Ciao,
Holger

[boris55]

Hi Holger,
I am beginning to see why you are a Forum Guru.... you have managed to streamline my clumsy attempt, I have been trying to expand your code to work on 19 columns, all in the same format as column 'A' but I'm getting some strange results. Is it a case of putting the routine into another loop?

Regards

Boris

[HaHoBe]

Hi, Boris,

as I didnīt see the criteria for choosing the column so I set a constant for this (Const clngCOLUMN As Long = 2) assuming this was all to be filled in into the first available Column on Sheet Master. If you want a loop you must enter a variable for the constant and change the value for it if a new one should be used (thatīs exactly where I had problems finding out).

Ciao,
Holger

[boris55]

Hi Holger,
I have amended your code but I am struggling to change where it looks for the data, it works to a degree but only copies column A to all other columns.
I have put a full set of data in ' Data Hall 1' of the attached spread sheet (101)
I tested it on 'Row 2' sheet and found if there's no result, i.e. row 13 & 15, it puts the column header in there, can this be avoided?

[HaHoBe]

Hi, Boris,

maybe try this amended code taking care of the fact that there must a row of data (not only the header row):

Please Login or Register  to view this content.

Ciao,
Holger

[boris55]

Hi Holger,

You truly are a guru.
Thanks very much, you have saved me a lot of searching and head scratching.

Best regards

Boris