VBA: Unable to reference Range in another sheet

Question

This is my first post, so please provide any feedback about my approach to presenting the problem.

I'm building a sub that (ultimately) is supposed to copy a range from one sheet ("Sandbox") to another ("Master"). The steps are:

Identify the selected rows
Loop through the Sandbox rows, determining whether to find a matching Master row or add as a new end-row in Master
Copy the values only from each selected Sandbox row to the appropriate Master row

The error pops with the setting the range for the PasteSpecial function. That line consistently gives a "1004 (Method 'Range' of object '_Global' failed" message.

Here's the code :

Sub UpdateMaster()

    Dim currentSelection As Range
    Set currentSelection = Selection

    Dim sheetSB As Worksheet
    Set sheetSB = ThisWorkbook.Sheets("Sandbox")
    Dim sheetMaster As Worksheet
    Set sheetMaster = ThisWorkbook.Sheets("Master")

    Dim lastTargetRow As Integer
    lastTargetRow = sheetMaster.Range("IDRange").End(xlDown).Row + 1
    Dim startingTargetColumn As Integer
    startingTargetColumn = sheetMaster.Range("IDRange").Column

    Dim thisID As String
    Dim thisStatus As String

    For Each thisrow In currentSelection.Rows
        ' Capture the current ID value
        thisID = Cells(thisrow.Row, Range("IDRange").Column).Value
        ' Capture the current Status value
        thisStatus = Cells(thisrow.Row, Range("NewRange").Column).Value

        ' If the row has no ID...
        If thisID = "" Then
            ' ...do nothing
          ' If the row is flagged as new...
          ElseIf thisStatus = "New" Then
            '...identify the first blank row, and set all data columns to be copied
            Range(Cells(thisrow.Row, Range("IDRange").Column), Cells(thisrow.Row, Range("LastSandboxColumn")).Column).Copy _
              Destination:=sheetMaster.Range(lastTargetRow, startingTargetColumn)

            ' Increment the next available last row by 1
            lastTargetRow = lastTargetRow + 1
          Else
          ' Otherwise, find the corresponding row and set the non-ID columns to be copied
            Dim sourceColumn1 As Integer, sourceColumn2 As Integer
            Dim targetRow As Integer, targetColumn As Integer
            Dim matchRow As Integer

            sourceColumn1 = Range("IDRange").Column + 1
            sourceColumn2 = Range("LastSandboxColumn").Column

            targetRow = Application.WorksheetFunction.Match(thisID, sheetMaster.Range("IDRange"), 0)
            targetColumn = startingTargetColumn + 1

            Range(Cells(thisrow.Row, sourceColumn1), Cells(thisrow.Row, sourceColumn2)).Copy
            Range(sheetMaster.Cells(targetRow, targetColumn)).PasteSpecial xlPasteValues
        End If
    Next
End Sub

The error is happening on the last line:

Range(sheetMaster.Cells(targetRow, targetColumn)).PasteSpecial xlPasteValues

Inexplicably, the following seems to work:

Range(Cells(thisrow.Row, sourceColumn1), Cells(thisrow.Row, sourceColumn2)).Copy _
  Destination:=Range(sheetMaster.Cells(targetRow, targetColumn))

Unfortunately, I want only the values; bringing over formulas and formatting will screw up other behavior in the sheet.

I have tried many variations, but essentially it will not allow me to create a range, referencing Master, if I use Cells().

Any help much appreciated.

Try `sheetMaster.Cells(targetRow, targetColumn).PasteSpecial xlPasteValues` — David Zemens, Nov 14 '16 at 15:27

score 2 · Accepted Answer · edited May 23 '17 at 12:07

2

Just do:

sheetMaster.Cells(targetRow, targetColumn).PasteSpecial xlPasteValues

An error could occur with this, if the sheetMaster isn't the ActiveSheet at runtime:

Range(sheetMaster.Cells(targetRow, targetColumn).PasteSpecial) xlPasteValues

Also note, for this problem:

Unfortunately, I want only the values; bringing over formulas and formatting will screw up other behavior in the sheet.

You can get the range's .Value as an array, and write it directly to the other sheet without invoking either Copy or Paste/PasteSpecial methods. The answer below shows several methods of copying/pasting from one workbook to another, but could easily be modified for sheet-to-sheet transfer, instead.

Copy from one workbook and paste into another

edited May 23 '17 at 12:07

Community

1
1

answered Nov 14 '16 at 15:28

David Zemens

53,033
11
81
130

Thanks for the response. Just for clarification: I've tried "sheetMaster.Range(...", and it rejects it. If I wanted to act on a Range in another sheet, how would I do that? – Scott Grabo Nov 14 '16 at 15:43
Sorry, one more thought: I found that using "Range(sm.Cells(x, y), sm.Cells(x, y)" DOES work (actually using a duplicate start/end set of Cells references). Strange behavior... – Scott Grabo Nov 14 '16 at 15:45

VBA: Unable to reference Range in another sheet

1 Answers1