Copy underlined text

[redspanna]

Hi all,

Let me try explain what I'm trying to achieve..

A worksheet is activated using the ref from cell A1 of the 'Front' sheet, so if A1=3 then sheet called '3' is activated.

Then from row 39 of that particular sheet (Column C) copy any text that is Underlined then paste this into the next available row Column A of the workbook 'Master Report Generator' sheet 'Report Data'

for example if Sheet named '3' is selected and Column C (from row 39) has the following data...

This is a test
to see if it fails
to see if it works
and it probably didn't
hopefully it will

then the data copied into the workbook 'Master Report Generator' sheet 'Report Data' will be

This is a test
to see if it works
hopefully it will

this is the code I have, but doesn't quite seem to do as I wish..

Sub Copy_Underlined()

Application.ScreenUpdating = False
Dim MY_ROWS As Long
Dim REPORT_DATA As Worksheet
Dim LAST_ROW As Long
Sheets("front").Select
Range("M15").Select
Selection.Copy

For Each WB In Workbooks
If WB.Name Like "Counters Report*" Then
WB.Activate
Sheets("Front").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheets(Worksheets("Front").Range("A1").Value).Activate


Set REPORT_DATA = Workbooks("Master Report Generator").Sheets("Report data")
If REPORT_DATA.Range("A1").Value = "" Then
LAST_ROW = 1
Else
LAST_ROW = REPORT_DATA.Cells(REPORT_DATA.Rows.Count, 1).End(xlUp).Row + 1
End If

With Worksheets(Worksheets("Front").Range("A1").Value)
For MY_ROWS = 41 To 250
If .Range("C" & MY_ROWS).Font.Underline = xlUnderlineStyleSingle Then
REPORT_DATA.Cells(LAST_ROW, 1).Value = .Range("C" & MY_ROWS).Value
LAST_ROW = LAST_ROW + 1
End If
Next MY_ROWS
End With
End If
Next

end sub

hope this makes sense, and appreciate any help

 

[Peter_SSs]

this is the code I have, but doesn't quite seem to do as I wish..

If my suggestion below does not do what you want, you had better explain exactly what you mean by the above. That is,
- What does it do that it shouldn't?
- What doesn't it do that it should?

Also, in future please post indented code. It is very hard to read & debug code that is all left-aligned.

Try making this change near the end of your code.

<del>REPORT_DATA.Cells(LAST_ROW, 1).Value = .Range("C" & MY_ROWS).Value</del>
.Range("C" & MY_ROWS).Copy Destination:=REPORT_DATA.Cells(LAST_ROW, 1)