CODEWITHSUNDEEP
excelpastevba
Scott Grabo
Scott Grabo

Reputation: 43

VBA: Unable to reference Range in another sheet

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:

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.

Upvotes: 4

Views: 798

Answers (1)

David Zemens
David Zemens

Reputation: 53623

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

Upvotes: 3

Related Questions