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]

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

did not know that, thank you for this knowledge

+1

 

[Rick Rothstein]

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

I just realized that you were trying to do two operations within one line of code (you can't do that). Try replacing this line of code...

[table="width: 500"]
[tr]
	[td]Sheets("Historical").Cells(Lastrow, "B").Copy Sheets("Data").Cells(Newproj - Masterrow + 1, "C").PasteSpecial xlFormats[/td]
[/tr]
[/table]

with these two lines of code...

[table="width: 500"]
[tr]
	[td]Sheets("Historical").Cells(LastRow, "B").Copy
Sheets("Data").Cells(Newproj - Masterrow + 1, "C").PasteSpecial xlFormats[/td]
[/tr]
[/table]

 

[theteerex]

I just realized that you were trying to do two operations within one line of code (you can't do that). Try replacing this line of code...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sheets("Historical").Cells(Lastrow, "B").Copy Sheets("Data").Cells(Newproj - Masterrow + 1, "C").PasteSpecial xlFormats[/TD]
[/TR]
</tbody>[/TABLE]

with these two lines of code...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sheets("Historical").Cells(LastRow, "B").Copy
Sheets("Data").Cells(Newproj - Masterrow + 1, "C").PasteSpecial xlFormats[/TD]
[/TR]
</tbody>[/TABLE]

Hi Rick, your code makes it so the cell is selected but not pasted. Is something missing at the end? Should the PasteSpecial be at the beginning maybe?

 

[theteerex]

I just realized that you were trying to do two operations within one line of code (you can't do that). Try replacing this line of code...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sheets("Historical").Cells(Lastrow, "B").Copy Sheets("Data").Cells(Newproj - Masterrow + 1, "C").PasteSpecial xlFormats[/TD]
[/TR]
</tbody>[/TABLE]

with these two lines of code...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sheets("Historical").Cells(LastRow, "B").Copy
Sheets("Data").Cells(Newproj - Masterrow + 1, "C").PasteSpecial xlFormats[/TD]
[/TR]
</tbody>[/TABLE]

Your code worked with a modification. I added an underscore after copy so the two lines were linked.

 

[theteerex]

This code is now pasting twice. Is there something I am doing wrong?
I tried modifying it based on the feedback I just received.

Sub AddProj() 'Adds new template to Data Worksheet


Sheet1.Range("Master").Copy
Sheet1.Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlFormats
FindProj


End Sub

 

[Rick Rothstein]

This code is now pasting twice. Is there something I am doing wrong?
I tried modifying it based on the feedback I just received.

Sub AddProj() 'Adds new template to Data Worksheet

Sheet1.Range("Master").Copy
Sheet1.Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlFormats
[B][COLOR="#FF0000"]FindProj[/COLOR][/B]

End Sub

Does FindProj call AddProj by any chance?

 

[theteerex]

Does FindProj call AddProj by any chance?

FindProj does not call AddProj.
AddProj copies a range and then calls FindProj which pastes a value in the first cell of that range.

Initially, I was thinking about it wrong and just realized that I wanted the range to have the same formatting.
FindProj simply pastes in a value.

So I tried what you said earlier and the code just doesn't work properly.

 

[pmich]

Please see this:
https://www.mrexcel.com/forum/excel...ion-address-cell-values-another-workbook.html