Excel VBA Paste Special Values not working

[ben_sorensen]

I think this should be relatively easy to answer but my code keeps popping an error and I can't figure out why I am trying to take a section of another workbook and paste the values in a section of the workbook that I am working in:

Sub GetDemandPlan1()


With Sheets("Paste Day 1 Demand Plan Here")
.Rows(3 & ":" & .Rows.Count).Delete
End With


Workbooks("Demand Planning Shop X for 01-27-2018.xlsx"). _
Sheets("Demand Planning").Range("A3:DZ250").Copy _
Sheets("Paste Day 1 Demand Plan Here").Range("A3:DZ250") _
.PasteSpecial paste (xlPasteValues, Operation:=xlNone, skipblanks:= False, _
Transpose:= False






End Sub

Any help would be appreciated.

 

[Paul Ked]

Not tested, but you have a line break before the .PasteSpecial, which won't compile. Try:

Sub GetDemandPlan1()
    With Sheets("Paste Day 1 Demand Plan Here")
        .Rows(5 & ":" & .Rows.Count).Delete
    End With
    Workbooks("Demand Planning Shop X for 01-27-2018.xlsx").Sheets("Demand Planning").Range("A3:DZ250").Copy
    Sheets("Paste Day 1 Demand Plan Here").Range("A3:DZ250").PasteSpecial _
        Paste(xlPasteValues, Operation:=xlNone, skipblanks:=False, Transpose:=False)
End Sub

 

[ben_sorensen]

Thanks for the reply.

Now I am getting a different error altogether. I'm getting a sub or function not defined....I tried putting a period (.) in from of the paste and that just gives me a syntax error.

Not sure what is going on?

Any advice would be appreciated!

Sub GetDemandPlan1()


With Sheets("Paste Day 1 Demand Plan Here")
.Rows(3 & ":" & .Rows.Count).Delete
End With


'Workbooks("Demand Planning Shop 101 for 01-27-2018.xlsx"). _
'Sheets("Demand Planning").Range("A3:DZ250").Select _
' With Selection
' .Copy

' Sheets("Paste Day 1 Demand Plan Here").Range("A3") _
'.PasteSpecial Paste:=xlValues
'End With
' Application.CutCopyMode = False



Workbooks("Demand Planning Shop 101 for 01-27-2018.xlsx").Sheets("Demand Planning").Range("A3:DZ250").Copy
Sheets("Paste Day 1 Demand Plan Here").Range("A3:DZ250").pasteSpecial _
Paste(xlPasteValues, Operation:=xlNone, skipblanks:= False, Transpose:=False)




End Sub

 

[Paul Ked]

You didn't copy and paste correctly.

Your original error was because the line break had split up the

Sheets("Paste Day 1 Demand Plan Here").Range("A3:DZ250").PasteSpecial

into

Sheets("Paste Day 1 Demand Plan Here").Range("A3:DZ250")
.PasteSpecial

which is like split
ting up wor
ds in the wro
ng place.

When you have a long line of code like this:

Sheets("Paste Day 1 Demand Plan Here").Range("A3:DZ250").PasteSpecial xlPasteValues, Operation:=xlNone, skipblanks:=False, Transpose:=False

You can put a line break in it using under score and return, like this:

Sheets("Paste Day 1 Demand Plan Here").Range("A3:DZ250").PasteSpecial _
    xlPasteValues, Operation:=xlNone, skipblanks:=False, Transpose:=False

But only after an actual 'sentence'. If you try and enter these two lines separately, the compiler is looking for a sub or function called Paste or PasteSpecial (at the beginning of a NEW line, not a line break), which doesn't exist, because it doesn't recognise the line break.

Copy and paste the 3rd line of code above and it will work. Put the cursor just before the PasteSpecial and type _ and enter. The code will still compile. For neatness and ease of reading indent the second line (as can be seen in the last bit of code above).

I hope you understand now.

Cheers

PS Please use code tags when posting code.

 

[ben_sorensen]

Wow thanks! Total newb here, thanks for being patient.

best