CODEWITHSUNDEEP
excelvba
fedone
fedone

Reputation: 55

Paste same value across multiple cell in different sheet

I have to copy paste some data from a sheet to another in excel using VBA. I have been able to copy-paste the first set of data from sheet A to sheet B without issues. I am now at the point where I need to copy the same cell value in sheet A into a range in sheet B

What I tried was to define the lastcell in sheet B and the first cell in sheet B in order to define the range where the value in sheet A should be copied.

This is the code for lastrow (which is working fine)

Last_Row2 = Sheets("Records").Range("a1").End(xlDown).Row

code for starting cell

legrng = Sheets("Records").Cells(Rows.count, 4).End(xlUp).Offset(1)

code for pasting the value into the range

Range("LegRng & Last_row2").Copy Destination = "cell value in sheet A"

I am receiving an error here:

Range("LegRng & Last_row2").Copy Destination = "cell value in sheet A"

the error is : Method 'Range of object_global' failed

Could you please tell me what I am doing wrong?

thank you for the help/explanation

Upvotes: 0

Views: 562

Answers (2)

GMalc
GMalc

Reputation: 2628

If not wrapped within an If Statement that tests if startCell.Row is less then Last_Row2, the accepted answer will cause issues if re-run, the code will place the value from sheet A in the next cell each time the macro is re-run.

You should always define and assign your variables.

Using Resize allows you to not need endCell.

Removing Offset from the startCell ensures the correct count for Resize

Sub FillInBlankCellsWithValue()
Dim Last_Row2 As Long, startCell As Range

    With Sheets("Records")
        Last_Row2 = .Range("a1").End(xlDown).Row
        Set startCell = .Cells(Rows.Count, 4).End(xlUp)

        'Use the If statement to test that the startCell.Row is less then the Last_Row2 
        If startCell.Row < Last_Row2 Then

            'Offset to the first empty cell and resize by subtracting the startCell.Row
            'from Last_Row2, to set the range for the value from SheetA.
            startCell.Offset(1).Resize(Last_Row2 - startCell.Row).Value = Sheets("SheetA").Range("A1")
            'Change the worksheet and paste value's cell range, as needed


        End If
    End With
End Sub

Upvotes: 0

mielk
mielk

Reputation: 3940

You have a couple of errors

First, you try to refer to the range which has name LegRng & Last_row2. Such range doesn't exist in your worksheet, it can't exist because this name is illegal.

If you want to set reference to the range containing more than one cell you do it this way:

Sheets("Records").Range(startCell, endCell)

So first you need to set references to startCell and endCell (I assume you want last cell to be also in column 4):

Set startCell = Sheets("Records").Cells(Rows.count, 4).End(xlUp).Offset(1)
Set endCell = Sheets("Records").Cells(Last_Row2, 4)

You can fill range with value using its property Value

rng.Value = "value"

So, at the end your code should look like that:

With Sheets("Records")
    Last_Row2 = .Range("a1").End(xlDown).Row
    Set startCell = .Cells(Rows.count, 4).End(xlUp).Offset(1)
    Set endCell = .Cells(Last_Row2, 4)
    .Range(startCell, endCell).value = "cell value in sheet A"
End With

Upvotes: 1

Related Questions