Copying Multiple Cells in VB

**Adam1986** · 02-24-2010, 05:50 AM

Hi

The basic on what i want to be able to do if possible is:

If 2 or more cells are selected (below one another) and i press run, then Copy the info in the top cell to 1 sheet, and copy the info in the 2nd cell, into a 2nd sheet.

I also need to make sure that if only 1 cell is selected, that it only copies the info from that single cell.

Is this possible? Any advise would be great

Cheers

**Domski** · 02-24-2010, 06:01 AM

Maybe:

Code:

Sub test()

Dim myRange As Range, myCount As Integer

Select Case Selection.Cells.Count

Case 1

    Selection.Copy Sheets("Sheet2").Range("A1")

Case 2

    For Each myRange In Selection
    
        For myCount = 2 To 3
    
        myRange.Copy Sheets("Sheet" & myCount).Range("A1")
        
        Next myCount
        
    Next myRange

Case Is > 2

    Exit Sub

End Select

End Sub

If 1 cell is selected then the cell will just be copied to Sheet2, if 2 then they will be copied to Sheet2 and Sheet3.

Dom

**Adam1986** · 02-24-2010, 07:23 AM

Thanks for that Dom, but its not quite what i was after.

Its still better than anything i've come up with so far....

At the minute, If 2 cells selected it copies the same the same value to sheet 2 and sheet 3.
Ideally i need it to copy the 1st cell value into sheet 2 and the 2nd cell value into sheet 3

**Domski** · 02-24-2010, 07:38 AM

Aye, it wasn't quite right. Try:

Code:

Sub test()

Dim myRange As Range, myCount As Integer

Select Case Selection.Cells.Count

Case 1

    Selection.Copy Sheets("Sheet2").Range("A1")

Case 2

    myCount = 2

    For Each myRange In Selection
    
        myRange.Copy Sheets("Sheet" & myCount).Range("A1")
        
        myCount = myCount + 1
        
    Next myRange

Case Is > 2

    Exit Sub

End Select

End Sub

Dom