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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Scattered array cells copy to scattered array cells another workbook 14
Save As and new workbook new name used in existing code 8
Cell values to an array, then array transpose to another workbook 8
VBA code for pasting links -- modifying RDB code for summary workbook 10
Copy value from changing cells without VBA 1
Code does not error but no copy either 4
Arrays and scattered cell values or another way? 16
how to copy worksheet to new workbook with values only 3

Back
Top