Help Copying Certain Cells

[EB08]

<HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1> <!-- / icon and title --><!-- message -->
I don't have access to the file right at this moment...but I will try to describe it the best I can. In Column A there is an empty cell with a gray background followed by about 5 cells of text (space between gray cells varies). I need to (if possible) be able to write something that looks for each gray cell in column A, copies the next two cells down from that gray cell, and pastes in in another sheet. That is probably the best I can do without seeing the sheet again...Any tips or advice is greatly appreciated, I'm not expecting anybody to write the code, but some pointers in the right direction would be great.

 

[EB08]

I'm using Excel 2003 FYI...Does this even sound possible?

 

[EB08]

I have got this to start in the right direction, it will go to the first cell with a gray background, copy the two next cells below(or however many I choose with resize) and it will paste them on the next sheet where I want them. The problem now is that it stops after the first one and gives me the error..."Select method of Range class failed" *run-time error '1004'
Any Help??? Forgot to mention I'm a newbie to VB, probably is obvious with the look of the code below.

Sub Test()
Dim C As Range

For Each C In Range("A1:A30")
If C.Interior.ColorIndex = 15 Then
C.Offset(1).Resize(2).Select
Selection.Copy
Sheets("sheet2").Activate
Range("D5").Select
ActiveSheet.Paste

End If
Next

End Sub

 

[EB08]

Now have the problem of stopping after the first gray cell fixed...problem now being...it keeps pasting over the previous paste, Im sure this is an easy fix but like I said, I'm new to this and don't have the slightest clue how to have this happen. the code now looks like...

Sub Test()
Dim C As Range

For Each C In Range("A1:A30")
If C.Interior.ColorIndex = 15 Then
C.Offset(1).Resize(2).Select
Selection.Copy
Sheets("sheet2").Activate
Range("D5").Select
ActiveSheet.Paste
Sheets("sheet1").Activate

End If
Next


End Sub

 

[doofusboy]

You need to iterate the address of the target cell to paste to so that it doesn't keep pasting to the same cell. Something like this should work, assuming you want to start pasting at cell D5:

i = 5
For Each C In Range("A1:A30")
    If C.Interior.ColorIndex = 15 Then
        C.Offset(1).Resize(2).Select
        Selection.Copy
        Sheets("sheet2").Activate
        Range("D" & i).Select
        ActiveSheet.Paste
        Sheets("sheet1").Activate
        i = i + 1
    End If
Next

 

[erik.van.geit]

Hi,

a few questions

Are there also blank cells which are not grey in that column?
I mean: would looking to the blank cells work also?

If not: is this grey the result of a "real" color or is it the result of a conditional format?

kind regards?
Erik

EDIT: And what grey did you use?
record a macro using that grey and let us know the number

 

[EB08]

Thanks for the help, that part is now fixed!...Running into one issue though, it seems that the resize(2) is only working on the very last one. In other words, after each gray cell I want it to copy the next two cells and paste them over to the next sheet. This is doing everything correct except that for every gray cell before the last, it only copies/pastes the first cell after the gray cell instead of the first and second cell...Am I making sense here?

 

[EB08]

a few questions

Are there also blank cells which are not grey in that column? Yes
I mean: would looking to the blank cells work also? never the same number of blank cells...not sure exactly what you mean.

If not: is this grey the result of a "real" color or is it the result of a conditional format? somebody colored them grey to visually seperate the data

And what grey did you use? colorindex = 15

Somebody at work put together an insane list that follows no real format at all...the only thing I have to work with is that the 2 or 3 cells below each grey cell in the column "A" need to be copied and pasted into the next sheet...As I cant download ANYTHING at work, I cant post an example of what this looks like...here is my best attempt at an example...

grey cell
text i need
text i need
text i need
dont need
dont need
blank
grey cell
text i need
text i need
text i need
blank
blank
blank
blank
grey cell
so on...

 

[erik.van.geit]

you don't need to download anything
you can select your range, copy and paste

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; WIDTH: 48pt; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>header</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: #c0c0c0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>A11</TD></TR></TBODY></TABLE>

now I'll see what I can do

 

[EB08]

This worked as far as finding each grey cell and then copying below the cell and pasting where I want it, the issue now is the fact that the only time it copies the number of cells I want (Resize(2)) is after the very last grey cell...What do you think is causing this?

i = 5
For Each C In Range("A1:A30")
If C.Interior.ColorIndex = 15 Then
C.Offset(1).Resize(2).Select
Selection.Copy
Sheets("sheet2").Activate
Range("D" & i).Select
ActiveSheet.Paste
Sheets("sheet1").Activate
i = i + 1
End If
Next

if column A looked like...
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=64 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>8</TD></TR></TBODY></TABLE>
it copies over to sheet 2 column d as

1
3
5
7
8

Leaving out 2, 4, and 6 but copying 7 and 8 both???