Paste value in new workbook

  • Thread starter Thread starter Elton Law
  • Start date Start date
E

Elton Law

Dear expert,
Wanna copy and paste value of range Q1:R20 in sheet1 to a new workbook.
Is that difficult?
Thanks
 
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
 
Hi Jacob,
It works 50% ...
But it displays #REF! ...
Can you teach me how to paste values only rather than formulas?
Thanks
Elton
 
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.
 
Work now work now .........
Thanks so much

Jacob Skaria said:
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.
Click to expand...
 
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
 
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
 
Thanks
All solve.
Thanks
Elton

Jacob Skaria said:
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
Click to expand...
 
Back
Top