Paste value in new workbook
 

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


--
Jacob


"Elton Law" wrote:

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

Paste value in new workbook
 

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" wrote:

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


--
Jacob


"Elton Law" wrote:

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

Paste value in new workbook
 

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.

--
Jacob


"Elton Law" wrote:

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" wrote:

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


--
Jacob


"Elton Law" wrote:

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

Paste value in new workbook
 

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

"Jacob Skaria" wrote:

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.

--
Jacob


"Elton Law" wrote:

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" wrote:

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


--
Jacob


"Elton Law" wrote:

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

Paste value in new workbook
 

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

"Elton Law" wrote:

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

"Jacob Skaria" wrote:

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.

--
Jacob


"Elton Law" wrote:

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" wrote:

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


--
Jacob


"Elton Law" wrote:

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

Paste value in new workbook
 

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


--
Jacob


"Elton Law" wrote:

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

"Elton Law" wrote:

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

"Jacob Skaria" wrote:

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.

--
Jacob


"Elton Law" wrote:

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" wrote:

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


--
Jacob


"Elton Law" wrote:

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

Paste value in new workbook
 

Thanks
All solve.
Thanks
Elton

"Jacob Skaria" wrote:

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


--
Jacob


"Elton Law" wrote:

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

"Elton Law" wrote:

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

"Jacob Skaria" wrote:

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.

--
Jacob


"Elton Law" wrote:

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" wrote:

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


--
Jacob


"Elton Law" wrote:

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