copy array to new sheet

I am trying to automate the copying of values from a particular array on
one sheet of a work book to a blank sheet in the same work book.

The sheet that the values come from contains formulas and values, I am
only after the resulting values not the formulas

As this workbook is used as a template for altering differing sets of
data, the size of the array varies by the number of rows populated. The
number of columns remains the same.

Will this require a VB script?
Any suggestions.?


---
Message posted from http://www.ExcelForum.com/

Since you haven't shared any specifics about worksheet names or the
columns that contains the info to be copied, I'll let you customize the
code below.

Suppose the data to be copied are in columns C:E, start in row 2, have
at least 2 rows, and are contiguous. Then, the code below should do
the job.

Sub testIt()
Dim DestSheet As Worksheet, SrcSheet As Worksheet
Set SrcSheet = ActiveSheet
Set DestSheet = SrcSheet.Parent.Worksheets.Add
With SrcSheet
.Activate
Range(.Range("c2"), .Range("c2").End(xlDown)).Resize(, 3).Copy
End With
DestSheet.Range("a1").PasteSpecial xlPasteValuesAndNumberFormats
End Sub


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , bypass
says...
I am trying to automate the copying of values from a particular array on
one sheet of a work book to a blank sheet in the same work book.

The sheet that the values come from contains formulas and values, I am
only after the resulting values not the formulas

As this workbook is used as a template for altering differing sets of
data, the size of the array varies by the number of rows populated. The
number of columns remains the same.

Will this require a VB script?
Any suggestions.?


---
Message posted from http://www.ExcelForum.com/

Thanks Tushar

this looks like what I am after

However I am not familiar with VB and there is an error.

If I debug, this is where it is indicating a problem

DestSheet.Range("a1").PasteSpecial xPasteValuesAndNumberFormats


It did select the correct range and added in the new sheet but that is
as far as it got

any ideas?


---
Message posted from http://www.ExcelForum.com/

You really need to provide more specific information.

What error message do you get? What version of XL are you using?

I tested the code with XL2003 before posting it.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , bypass
says...
Thanks Tushar

this looks like what I am after

However I am not familiar with VB and there is an error.

If I debug, this is where it is indicating a problem

DestSheet.Range("a1").PasteSpecial xPasteValuesAndNumberFormats


It did select the correct range and added in the new sheet but that is
as far as it got

any ideas?


---
Message posted from http://www.ExcelForum.com/

Sorry

ECXEL ver is 2000

error is run-time error '1004':
PasteSpecial method of Range class failed.

hope that helps


---
Message posted from http://www.ExcelForum.com/

Ok, I don't know what is going on. If I try to run the code in XL2000,
I get a compile-time error 'Variable not defined' and the VBE points to
xlPasteValuesAndNumberFormats

Did you change anything in the code I posted?

Also, what do cells C2:C3 contain *before* you run the macro?

As long as the conditions laid out in my first post are met, the code
below works with XL2000. Hopefully, it also works with later versions.

Sub testIt()
Dim DestSheet As Worksheet, SrcSheet As Worksheet
Set SrcSheet = ActiveSheet
Set DestSheet = SrcSheet.Parent.Worksheets.Add
With SrcSheet
.Activate
Range(.Range("c2"), .Range("c2").End(xlDown)).Resize(, 3).Copy
End With
With DestSheet.Range("a1")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , bypass
says...
Sorry

ECXEL ver is 2000

error is run-time error '1004':
PasteSpecial method of Range class failed.

hope that helps


---
Message posted from http://www.ExcelForum.com/

Thanks for that Tushar

That did the trick.

Thanks for your patience


---
Message posted from http://www.ExcelForum.com/