Copying Cells to new worksheet based on formatting using VBA

[lshaw79]

Hi,

I'm kinda new to this so please bear with and if you suspect I may have missed some information out please ask me to clarify and I will do the best I can. Basically my initial query is as follows.
I have some information in an excel worksheet that needs to be copied into another worksheet, in the same file.
The reason for this is because the way the original worksheet is laid out it's not very easy to understand or work with so I need a method of selecting the various pieces of information and putting it into a more tabular format on the new worksheet.
My question is really is it possible to select information in cells based on font formatting (e.g. bold, italic etc) and how would I go about making sure the right cells were selected and that they were copied to the right place?
I guess I might use some kind of case statement to handle the selection, but how do I code in the copy and paste routines.

Thanks for any help in advance, I'm sure more questions will follow,

Cheers,
Leo.

 

[SkipVought]

Hi,

Yes, its possible.

1. First you need a map that describes what you do when encountering each different format type.

Do you have such a map?

THEN...

2. you need a description of the source data sheet in order to determine how to access that data.

THEN...

3. you need a description of the target data sheet in order to determine how to process each different type of source data (might be the same as 1 or more detailed)

THEN...

you can decide what options are available for performing this logic.

Skip,

_[red][/red]

 

[lshaw79]

Thanks for replying, though I'm afraid you will have to expand on your points a little more as I am having a little trouble understanding.

1. By this do you mean like a flowchart? In which case, no I don't. Simply it would be an If...Elseif...Else type statement, whereby if x is bold and not italic then it should be copied into a particular column on the new worksheet and so on.

2. and 3. Not entirely sure what you mean here, could you please expand on it a little and make it a bit clearer.

Thanks again,
Leo.

 

[SkipVought]

Case Bold
do THIS with the text
Case Italic
do OTHER with the text
Case Underline
do MORE with the text
........


Skip,

_[red][/red]

 

[lshaw79]

Ok thanks.

 

[SkipVought]

on 2, describe or post samples of how the sheet is constructed.

Is it in one table?

Is it in many tables?

Is it scattered over the sheet?

How would you describe the logic of accessing the data?

On 3, what is the structure of your table?

Skip,

_[red][/red]

 

[lshaw79]

With regards to the structure of the data in the source worksheet it is laid out like so;

[b]"Main Type"[/b]
[b][i]"Device Type"[/i][/b]
  "Device Description"
  "Device Description"
  "Device Description"
[b][i]"Device Type"[/i][/b]
  "Device Description"
  "Device Description"
[b]"Main Type"[/b]
[b][i]"Device Type"[/i][/b]
  "Device Description"
  "Device Description"
  "Device Description"
[b][i]"Device Type"[/i][/b]
  "Device Description"
  "Device Description"

and so on for as many as necessary. It's not scattered over the sheet as it just carries on down. Basically I think the logic for accessing the data would be as you said, using a case statement to identify what that particular piece of data is and then taking it and pasting it into the relevant cell on the new worksheet.

The structure I want to put in into would be more akin to an access table, like so;

"Main Type" "Device Type" "Device Description"

Thanks,
Leo.

 

[SkipVought]

Please post an axample of the source.

Are the QUOTES included in the data?

Is the data Indented or leading spaces?

Are the 3 font types...

BOLD

ITALIC

NEITHER

???

Skip,

_[red][/red]