How to select and copy

[Tompanilla]

In the end of a large macro I have this:

...
Dim i As Long
For i = 1 To Range("A65536").End(xlUp).Row
If IsError(Application.Find("Summa", Cells(i, 1), 1)) Then 'Cells(i,2).Font.Bold = False
Else
Cells(i, 1).Font.Bold = True 'Makes the selected cell in bold
Range(Cells(i, 7), Cells(i, 7)).Font.Bold = True

End If
Next i

End Sub

I would like the macro also to select all (i, 7) and then make a copy so I can paste them on another worksheet.
Have tryed to insert:
Cells(i, 7).Select
Cells(i, 7).Activate

I now that there is something missing as Cells(i, 7) needs to be added in a selection somehow before the activating or copy command.

Anyone that has the solution for me?

 

[Chippy]

Ok,
I haven't tested this as I don't have all the code but I'll explain the principle, that way if the below doesn't work then at least you might have an idea on what to do.

You create a string (in my example below it is called "strRange").
Everytime you make a cell bold, you add that cell address to the string.
Once you have finished making things bold, then you select the range based on the string.

Once again I have no idea if this will work. It seems Ok but you just never know.

Dim strRange as String
strRange=""

Dim i As Long 
For i = 1 To Range("A65536").End(xlUp).Row 
  If IsError(Application.Find("Summa", Cells(i, 1), 1)) Then 'Cells(i,2).Font.Bold = False 
  Else 
    Cells(i, 1).Font.Bold = True 'Makes the selected cell in bold 
    Range(Cells(i, 7), Cells(i, 7)).Font.Bold = True 

    strRange=strRange & Range(Cells(i, 7), Cells(i, 7)).address & ","

  End If 
Next i 

if strRange<>"" then
  strRange=left(strRange,len(strRange)-1)
  range(strRange).select
end if

 

[Tompanilla]

It works great!

I will now also enclose the copy command.

Thanks!

 

[Ronald Moore]

A couple of suggestions. It's often helpful to use range object variables. When possible, work on an entire range of cells when assigning properties such as Font.Bold or when using methods such as Copy. (This is what you would do when you manually invoke Copy, for example -- you would select the entire range to be copied rather than working one cell at a time.) Also, most of the time in VBA code, it's not necessary to select and activate worksheets and ranges to work with them. You can avoid explicit use of Paste by using the destination parameter with Copy. Finally, I don't quite understand the intent of your logic (is something intentionally commented out in the true branch of your conditional?), but in any case I believe you can use the Like operator rather than Application.Find. So I suggest something like the code below. In the destination parameter, change the sheet name to your desired destination sheet, and the range to the top cell of the desired destination range.

Sub MySub()
  Dim MyRange As Range, rCell As Range
  Set MyRange = Range([a1], [a65536].End(xlUp))
  For Each rCell In MyRange
    If rCell.Value Like "*Summa*" Then
      rCell.Font.Bold = True
    Else
      'offset(0,1) identifies cell 1 column to right of rCell
      rCell.Offset(0, 1).Font.Bold = False
    End If
  Next
  'offset(0,6) identifies range of cells 6 columns to right of MyRange
  MyRange.Offset(0, 6).Font.Bold = True
  MyRange.Offset(0, 6).Copy Destination:=Sheets("Sheet2").Range("A1")
End Sub