Help with Paste Special Vba

[theteerex]

I am wondering why the syntax is wrong here:

Sub FindProj() 'Finds project name in Historical Worksheet and pastes it in Data Worksheet
Dim Lastrow As Long
Dim Newproj As Long
Dim Master As Range
Dim Masterrow As Long


Masterrow = Worksheets("Data").Range("Master").Rows.Count
Lastrow = Sheets("Historical").Cells(Rows.Count, "B").End(xlUp).Row
Newproj = Sheets("Data").Cells(Rows.Count, "C").End(xlUp).Row
Sheets("Historical").Cells(Lastrow, "B").Copy Sheets("Data").Cells(Newproj - Masterrow + 1, "C")[COLOR=#ff0000].PasteSpecial xlFormats[/COLOR]


End Sub

Code works fine without it but I would also like to keep formatting so user can follow data easily.
Help please.

 

[Nine Zero]

i believe its xlPasteFormats

Here is a really awesome link to see all available

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlpastetype-enumeration-excel

 

[theteerex]

That didn't work out.

 

[Rick Rothstein]

i believe its xlPasteFormats

Both xlFormats and xlPasteFormats evaluate as -4122 so using either one is correct.

 

[Rick Rothstein]

I am wondering why the syntax is wrong here:

Sub FindProj() 'Finds project name in Historical Worksheet and pastes it in Data Worksheet
Dim Lastrow As Long
Dim Newproj As Long
Dim Master As Range
Dim Masterrow As Long


Masterrow = Worksheets("Data").Range("Master").Rows.Count
Lastrow = Sheets("Historical").Cells(Rows.Count, "B").End(xlUp).Row
Newproj = Sheets("Data").Cells(Rows.Count, "C").End(xlUp).Row
[COLOR="#FF0000"][B]Sheets("Historical").Cells(Lastrow, "B").Copy Sheets("Data").Cells(Newproj - Masterrow + 1, "C")[COLOR=#ff0000].PasteSpecial xlFormats[/COLOR][/B][/COLOR]


End Sub

Code works fine without it but I would also like to keep formatting so user can follow data easily.
Help please.

Try putting that ending xlFormats inside a set of parentheses...

Sheets("Historical").Cells(Lastrow, "B").Copy Sheets("Data").Cells(Newproj - Masterrow + 1, "C").PasteSpecial(xlFormats)

 

[theteerex]

The error I got was :
Run-time error '1004':

Unable to get the PasteSpecial property of the Range class.

 

[Rick Rothstein]

The error I got was :
Run-time error '1004':

Unable to get the PasteSpecial property of the Range class.

Which message were you responding to when you posted this?

 

[theteerex]

Which message were you responding to when you posted this?

I was responding to your message. Tried the modified code that you posted.

 

[Nine Zero]

try

.PasteSpecial Paste:=xlPasteFormats

 

[theteerex]

try

.PasteSpecial Paste:=xlPasteFormats

I got a:
Compile error:
Syntax error