Setting one range of cells equal to another without losing current selections

user_16314097 0
Viewed 53

I'm trying to fill a range of cells with the contents of another range of cells without losing the current selection(s), using VBA for Excel on Mac.

Why does

  Sheets("FormView").Range("C3") = Sheets("DataView").Range("E3")

work, but

  Sheets("FormView").Range("C3:D10") = Sheets("DataView").Range("E3:F10")

fail?

Can you suggest alternatives, please?

I could save the current selections and reselect them, but that would be admitting defeat...

vba excel
2 Answers

It might not be as neat as you like (and might be admitting your defeat a bit) but I'd copy the data without changing selection:

Sheets("DataView").Range("E3:F10").Copy
Sheets("FormView").Range("C3:D10").PasteSpecial

The selection will stay where it is but if you go to the "FormView" tab, it will have C3:D10 selected.

user_163140480

Copy Range Values by Assignment

Option Explicit

Sub CopyRange()
    
    Const sName As String = "DataView"
    Const sAddress As String = "E3:F10"
    
    Const dName As String = "FormView"
    Const dFirst As String = "C3"
     
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    Dim sws As Worksheet: Set sws = wb.Worksheets(sName)
    Dim srg As Range: Set srg = sws.Range(sAddress)
    
    Dim dws As Worksheet: Set dws = wb.Worksheets(dName)
    Dim dfCell As Range: Set dfCell = dws.Range(dFirst)
    Dim drg As Range: Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)
    
    drg.Value = srg.Value

End Sub

Sub CopyRangeNoConstants()
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    Dim sws As Worksheet: Set sws = wb.Worksheets("DataView")
    Dim srg As Range: Set srg = sws.Range("E3:F10")
    
    Dim dws As Worksheet: Set dws = wb.Worksheets("FormView")
    Dim dfCell As Range: Set dfCell = dws.Range("C3")
    Dim drg As Range: Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)
    
    drg.Value = srg.Value

End Sub

Sub CopyRangeNoConstantsNoWorksheets()
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    Dim srg As Range: Set srg = wb.Worksheets("DataView").Range("E3:F10")
    
    Dim dfCell As Range: Set dfCell = wb.Worksheets("FormView").Range("C3")
    Dim drg As Range: Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)
    
    drg.Value = srg.Value

End Sub
user_98140690
Related