Copying a range to a new worksheet

[Tom21]

What's the best way to copy the usedrange of a worksheet to a new worksheet created for this temporary purpose?

I've tried the Copy function, if this is correct I can't seem to get the syntax right.....any help appreciated.

Also I try to save the worksheet as a CSV file and it saves the entire workbook under a different name, instead of just the worksheet required (I only give the SaveAs function a worksheet as well).....

Thanks for you time,

Tom.

 

[dsi]

Why don't you copy the entire worksheet to a different workbook? This sample copies the worksheet 'Master' to a different workbook 'Book1'

Sheets("Master").Select
Sheets("Master").Copy Before:=Workbooks("Book1").Sheets(1)

 

[Tom21]

What I basically need to do is turn the contents of each worksheet into a set of CSV files.

D'you think that creating a new workbook would be the best way to do it?

Tom.

 

[Kaah]

Tom,

no, dont create new workbooks,

when you save a file through code into a csv format, only te activesheet is actually made into the csv (because obviously a text file cant contain multiple sheets), so take advantage of this and loop through each sheet in te workbook and save to CSV for each. Heres code:

for iCount to wksWorkbook.worksheets.count
wksWorkBook.worksheets(iCount).saveas "C:\temp\myCSV" & icount, xlcsv
next icount

I haven't tested this, but you get the idea anyway...you'll end up with a csv for each worksheet...
Above I have put the iCount index into the filename, make sure each filename is different otherwise they will overwrite each other..

hope this helps,

Kaah

 

[Tom21]

Gotcha,

Problem solved.

Cheers for the time and help,

Tom.

 

[fitchic77]

How do you Copy a range to the first available (blank) row an existing worksheet?

Thanks
Andrea

 

[Kaah]

Andrea (fitchic),

the easiest way to do it is using the usedrange property, the usedrange property of a worksheet is a system defined worksheet range that is inclusive of every used cell in the worksheet, its very usefull, but can be tricky sometimes (it is ideosyncratic)...here's code:

ActiveSheet.Range("myRange&quot.Copy
with thisworkbook.worksheets(2)
.Cells(.UsedRange.Cells(.UsedRange.Cells.Count).Row + 1, 1).PasteSpecial xlValues
end with

that second command (the long winded one) is where the first blank row in the worksheet is found. modify this statement to your hearts content, and email if you need any more help.

regards

kaah.