Copy Paste Macro Edit

[Alex O]

I thought this would paste values only, but for some reason it's pasting my formulas. How can this macro be edited to copy and paste values?

Thanks

Sub NewUploadFile()
Dim wb As Workbook
Set wb = Workbooks.Add

'copy and paste
ThisWorkbook.Worksheets(1).Range("B1:P2000").Copy _
Destination:=wb.Sheets("Sheet1").Range("A1")

wb.Sheets("Sheet1").Columns.AutoFit

Set wb = Nothing

 

[VoG]

Try

Sub NewUploadFile()
Dim wb As Workbook
Set wb = Workbooks.Add

'copy and paste
ThisWorkbook.Worksheets(1).Range("B1:P2000").Copy
wb.Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues

wb.Sheets("Sheet1").Columns.AutoFit

Set wb = Nothing

 

[njimack]

ThisWorkbook.Worksheets(1).Range("B1:P2000").Copy
wb.Sheets("Sheet1").Range("A1").PasteSpecial xlValues

 

[Alex O]

VoG
Your're solution works great, but I loose my formatting (borders, shading, etc.) any way to preserve that as well?

Thanks

 

[VoG]

Try

Sub NewUploadFile()
Dim wb As Workbook
Set wb = Workbooks.Add

'copy and paste
ThisWorkbook.Worksheets(1).Range("B1:P2000").Copy
With wb.Sheets("Sheet1")
    .Range("A1").PasteSpecial Paste:=xlPasteValues
    .Range("A1").PasteSpecial Paste:=xlPastenumberformats
    .Range("A1").PasteSpecial Paste:=xlPasteFormats
    .Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
End With
Set wb = Nothing

 

[paddydive]

VOG....

You are a excel rocker star.. dude...

 

[njimack]

Or

ThisWorkbook.Worksheets(1).Range("B1:P2000").Copy wb.Sheets("Sheet1").Range("A1")
 
With wb.Sheets("Sheet1").Range("A1:O2000")
.Formula=.Value
End With

 

[Alex O]

I'm getting Run-time error "1004": PasteSpecial method of range class failed message.

.Range("A1").PasteSpecial Paste:=xlPastenumberformats

Got the same result with njimack's suggestion....

 

[VoG]

Try

Sub NewUploadFile()
Dim wb As Workbook
Set wb = Workbooks.Add

'copy and paste
ThisWorkbook.Worksheets(1).Range("B1:P2000").Copy
With wb.Sheets("Sheet1")
    .Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    .Range("A1").PasteSpecial Paste:=xlPasteFormats
    .Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
End With
Set wb = Nothing

 

[Alex O]

Perfect....Thanks!