VBA Multiple selection (selection with CTRL)

[M Lievens]

Hello,

I'm trying to write a macro that copies all selected cells and paste it somewhere els.
My problem is with the selection part.

I'm using:
Dim CopyRng as range

Set CopyRng = selection

The problem:
If I select multiple rows with the CTRL hold in, it only copies the first rows.

Example:
If I select row 2,3,4 AND (CTRL) row 15,16 it will only copy row 2,3 and 4 but it should select my full selection (2,3,4 and 15,16)

Any ideas?

My full code:

Sub RangeToCSVexport()

Dim CopyRng As Range, FileNm As String

Set CopyRng = Selection
CopyRng.Columns("O:P").Select
Set CopyRng = Selection.SpecialCells(xlCellTypeVisible)

FileNm = "C:\temp\Computers.csv"

Application.ScreenUpdating = False

Workbooks.Add (1) 'A new workbook with a worksheet
With ActiveWorkbook
CopyRng.Copy .Sheets(1).Range("A2")
.SaveAs Filename:=FileNm, FileFormat:=xlCSV
.Close SaveChanges:=True
End With

Application.ScreenUpdating = True

End Sub

Thanks in advance!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub RangeToCSVexport()

Dim CopyRng As Range, FileNm As String

Set CopyRng = Intersect(Selection, Range("O:P"))

FileNm = "C:\temp\Computers.csv"

Application.ScreenUpdating = False

Workbooks.Add (1) 'A new workbook with a worksheet
With ActiveWorkbook
CopyRng.Copy .Sheets(1).Range("A2")
.SaveAs Filename:=FileNm, FileFormat:=xlCSV
.Close SaveChanges:=True
End With

Application.ScreenUpdating = True

End Sub

 

[M Lievens]

Hi & welcome to MrExcel.
How about

Sub RangeToCSVexport()

Dim CopyRng As Range, FileNm As String

Set CopyRng = Intersect(Selection, Range("O:P"))

FileNm = "C:\temp\Computers.csv"

Application.ScreenUpdating = False

Workbooks.Add (1) 'A new workbook with a worksheet
With ActiveWorkbook
CopyRng.Copy .Sheets(1).Range("A2")
.SaveAs Filename:=FileNm, FileFormat:=xlCSV
.Close SaveChanges:=True
End With

Application.ScreenUpdating = True

End Sub

Hi and thanks!

Works perfectly, i've added Selection.SpecialCells(xlCellTypeVisible) because of the filters.

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.