Why won't my range paste?

Question

The following is my VBA code, for some reason the code will run but not actually paste in the range I need it to paste. Anybody have any ideas why it won't paste my values?

The programs goes to my selected cell that I'm looking for, but now the activecell becomes my range and I'm trying to paste the it there. Any information will help, it just doesn't want to paste the values in the range I selected.

Sub Macro1()

Dim Form1033 As Worksheet
Dim CleaningSchedule As Worksheet

Set Form1033 = Worksheets("Form1033andForm1034")
Set CleaningSchedule = Worksheets("CleaningSchedule")

Dim Day As Range
Set Day = Form1033.Range("$J$3")

With Form1033

Range("$G$5:$G$18").Select
Selection.Copy

End With

With CleaningSchedule

Dim i As Integer

For i = 6 To 37

If Cells(4, i).Value = Day.Value Then

Cells(5, i).Select

Range(ActiveCell, Cells(ActiveCell.Rows + 13, ActiveCell.Column)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

                End If
          Next i
    End With


Form1033.Select
Application.CutCopyMode = False
Range("$G$5:$G$18").ClearContents

MsgBox "Scoresheet Updated"


End Sub

[How to Avoid Using Select and ActiveCell in VBA](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) - a **MUST** read for all VBA programmers. — Scott Holtzman, Jan 27 '16 at 17:25
In addition to @Scott's comment, you need to [assign parentage](http://stackoverflow.com/a/34934998/4650297) to the ranges when using `With`. So, in a `With` block, add `.` before any range type reference, such as `Range()`, `Cells()`, `Rows()`, `Columns()`, etc. Otherwise, it's just going to use whatever the `ActiveSheet` is for those references. — BruceWayne, Jan 27 '16 at 17:30

score 3 · Accepted Answer · answered Jan 27 '16 at 17:32

I fixed the code here, but please read the link I provided in my comment, and you will not have these errors in the future.

I also commented the refactors I did to the code. Also, notice that I assigned the Cells and Ranges to the parent worksheet with .. (See @BruceWayne's link in his comment to your original question)

Sub Macro1()

Dim Form1033 As Worksheet
Dim CleaningSchedule As Worksheet

Set Form1033 = Worksheets("Form1033andForm1034")
Set CleaningSchedule = Worksheets("CleaningSchedule")

Dim Day As Range
Set Day = Form1033.Range("$J$3")

'copy the range directly
Form1033.Range("$G$5:$G$18").Copy

With CleaningSchedule

    Dim i As Integer

    For i = 6 To 37

        If .Cells(4, i).Value = Day.Value Then

            'paste directly to range and i also combined 13 rows plus row 5, since you are always using the same row
            Range(.Cells(5,i), Cells(18,i)).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False

        End If

    Next i

End With

'clear contenst directly
Form1033.Range("$G$5:$G$18").ClearContents

MsgBox "Scoresheet Updated"

End Sub

THAT LINK IS THE BEST THING TO HAPPEN TO ME! Thank you so much — Sergio Oropeza, Jan 27 '16 at 17:36

Tango_Mike · Answer 2 · 2016-01-27T17:39:55.510

0

Since you are using "With" statement, you need to add a "." in front of "cells" and "range" and any other references you make. For example:

With myWorksheet
    .range("A1").copy
End with

So, the problem in this case is that you still remain on the same worksheet and clear the contents of the cells you had pasted.

edited Jan 27 '16 at 17:39

answered Jan 27 '16 at 17:32

Tango_Mike

104
11

Why won't my range paste?

2 Answers2