Hi All

I've managed to get stuck on a copy and paste issue. I'll describe what I'm trying to achieve, what I've done, and what the output is at this stage. Hopefully I make a little bit of sense ...

WHAT I'M TRYING TO ACHIEVE
* Created an Activity Calendar workbook, where the first tab (ACTIVITIES tab) is a list of different Activities, with various subsets of data in columns (Activity #, Activity Name, Owner, Presell Sales Drive, Activate Sales Drive, State, Channel, Brand etc. etc.) <= this is all done.
* Created another tab (1 SD CALENDAR) which will provide a calendar output (GANTT chart) based on certain criteria selected. So if I select Sales Drive 4, and click the "Populate Calendar" button it's meant to:
1. Clear contents from A12 to AT12 (the number of header columns in the ACTIVITIES tab). This also deletes the header names, so it goes back to ACTIVITIES and copies/pastes these back into 1 SD CALENDAR (conviluted, but it works, so I'm not phased).
2. Go to ACTIVITIES tab. Filter column N (Activitate Sales Drive - i.e. when will the activity be running) based on the Sales Drive selected. Copy all of the now filtered data.
3. Go to 1 SD CALENDAR tab. Paste data into sheet, which outputs conditional boxes, creating GANTT chart of Activities.
4. Go to ACTIVITIES tab. Unfilter. Filter column K (Presell Sales Drive - i.e. when will the activity be sold, usually, but not always one sales drive prior to Activity) based on Sales Drive selected previously.
5. Go to 1 SD CALENDAR tab. Move one row below Activity data. Paste Presell data, again populating the GANTT chart.

ISSUES:
* The way I've designed it, to first select, copy and paste the Activity data, I go to the ACTIVITIES tab, select cell B2 (the first header cell), ctrl++select+right, ctrl+select+down. I then copy all of this, including the header row and paste it all (again, including the header row) into the 1 SD CALENDAR tab, starting in cell B12. This works as the header row simply overwrites the old header row with the same info. The reason I did it this way is that if I was to go to the first header cell in ACTIVITIES, go down a cell, ctrl+right, ctrl+down, there may (and often are) blank cells, so I don't capture all of the data. As the rows are dependant on how many activities are in a sales drive, I can't define the data range (as far as I know).
* If I try to do the same with the Presell then I again have to select and copy the header row.
* I'm sure there's a way to select a variable range of data (more relevant to a variable number of rows to select, but always select/copy every column in the set range (i.e. A to AT)

Here's what I have so far:

Please Login or Register  to view this content.
Any help?

Thanks guys!

Cheers,
Evan