Deleting Rows in cycle

[jose001]

Hi all,

Could anyone help me clean this code up a little. It basically cycles through my sheets and pulls out data from certain cells.and puts it in the actions sheet.

Sub TransferData()
Dim i As Long: i = 6
For Each sh In Worksheets
If sh.Name <> "Actions" Then
Worksheets("Actions").Range("B" & i).Value = sh.Range("A2").Value
Worksheets("Actions").Range("C" & i).Value = sh.Range("B2").Value
i = i + 1
End If
Next sh
End Sub

However, I would like to add somthing like this in which will clear whatever is in the cells in actions already...

Range("B3:G500").Select
Selection.ClearContents

Sadly I'm very bad with VB and have no idea where to put it (if it is even the correct thing to do) without it wiping before it goes through the next sheet. Also I would really like to put a horizontal line under each entry from say B to G. Any help would be reaaaaally appreciated!

Jose

 

[ravishankar]

Hi
try putting the code Range("B3:G500").ClearContents before For Each sh This will clear the contents before cycle starts and be ready to receive new data
Ravi

 

[jose001]

Thanks Ravi, thats wicked, works a treat! How can I exclude another sheet called summary from this cycle?

 

[jose001]

OK Iv sorted this one now, found the parameter, anyone have any ideas how I can draw a line under each entry from b to g and colour each new row (btoG) with a different colour?

Jose

 

[ravishankar]

Hi
Try. Add these codes before i = i +1

Worksheets("Actions").Range("B" & i).interior.colorindex = i
Worksheets("Actions").Range("B" & i).select
 With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With

Ravi

 

[jose001]

That works brillaint Ravi but it only colours the cells in column B. Is there are way to colour the whole row from B to G? I'm guessing its something like...


Worksheets("Actions").Range("B" & i : "G" & i).Select

but I don't know the proper parameter

Thanks very much for your help!

Jose

 

[jose001]

Ok I found the parameter, just one final query, it is underlining any cell which is selected in the worksheet, how could I alter the code to only underline the cells b to g? and also maybe clear the colours and underline when it restarts?

Sub TransferData()
Dim i As Long: i = 6
Range("B6:G500").ClearContents
For Each sh In Worksheets
If sh.Name <> "Actions" And sh.Name <> "summary" Then
Worksheets("Actions").Range("B" & i).Value = sh.Range("A2").Value
Worksheets("Actions").Range("C" & i).Value = sh.Range("B2").Value
Worksheets("Actions").Range("b" & i & ":g" & i).Interior.ColorIndex = 1 + i
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
i = i + 1
End If
Next sh
End Sub

Thanks in advance

 

[ravishankar]

Hi
insert these codes before With Selection.Borders

x = "b" & i & ":g" & i
Range(x).Select
Range(x).interior.colorindex = x1None

RAvi