CODEWITHSUNDEEP
vbaexcel
Sergio Oropeza
Sergio Oropeza

Reputation: 53

Why won't my range paste?

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

Upvotes: 1

Views: 235

Answers (2)

Tango_Mike
Tango_Mike

Reputation: 104

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.

Upvotes: 0

Scott Holtzman
Scott Holtzman

Reputation: 27249

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

Upvotes: 3

Related Questions