Paste value in new workbook

[Elton Law]

Dear expert,
Wanna copy and paste value of range Q1:R20 in sheet1 to a new workbook.
Is that difficult?
Thanks

 

[Jacob Skaria]

Try the below. The destination is kept as 1st sheet Range A1. Change to suit.
Source would be the active sheet

Sub Macro()
Dim wb As Workbook, ws As Worksheet
Set ws = ActiveSheet
Set wb = Workbooks.Add
ws.Range("Q1:R20").Copy wb.Sheets(1).Range("A1")
End Sub

 

[Elton Law]

Hi Jacob,
It works 50% ...
But it displays #REF! ...
Can you teach me how to paste values only rather than formulas?
Thanks
Elton

 

[Jacob Skaria]

OK. Try the below.

Sub Macro()
Dim wb As Workbook, ws As Worksheet
Set ws = ActiveSheet
Set wb = Workbooks.Add
ws.Range("Q1:R20").Copy
wb.Sheets(1).Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

Try recording a macro and modify to suit.

 

[Elton Law]

Work now work now .........
Thanks so much

OK. Try the below.

Sub Macro()
Dim wb As Workbook, ws As Worksheet
Set ws = ActiveSheet
Set wb = Workbooks.Add
ws.Range("Q1:R20").Copy
wb.Sheets(1).Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

Try recording a macro and modify to suit.

 

[Elton Law]

Hi Jocab,
Can I ask one more question?
Can you tell me how to retain the format please?
I don't want to date to show up as 40180 .... wanna keep as 1 Dec 2009.
Thanks

 

[Jacob Skaria]

You can repeat the pastespecial...with different paste types

Sub Macro()
Dim wb As Workbook, ws As Worksheet
Set ws = ActiveSheet
Set wb = Workbooks.Add
ws.Range("Q1:R20").Copy
wb.Sheets(1).Range("A1").PasteSpecial xlPasteValues
wb.Sheets(1).Range("A1").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub

 

[Elton Law]

Thanks
All solve.
Thanks
Elton

You can repeat the pastespecial...with different paste types

Sub Macro()
Dim wb As Workbook, ws As Worksheet
Set ws = ActiveSheet
Set wb = Workbooks.Add
ws.Range("Q1:R20").Copy
wb.Sheets(1).Range("A1").PasteSpecial xlPasteValues
wb.Sheets(1).Range("A1").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub