Copy a Range into a new Excel-Sheet

Posted by Markus on May 29, 2001 2:01 AM

Hi,
i've got a problem. I have to copy a range from a existing Excel-sheet into a new one each time some Action is performed. I did it this way:
Set oExcel = New Application
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

vData = Tabelle1.Range("A1:M50")
oSheet.Range("A1:M50") = vData
oBook.SaveAs Tabelle1.Range("D4") & ".xls"
oExcel.Quit
Löschen.LöschTab
End Sub

But if i looked into this new Excel-sheet i found out that my formatings and colors i set are lost. So i wonder wether there is a method to transfer the whole settings (without the VBA-Macros i wrote) into this new Excel-sheet or wether i have do it all by hand. Can anyone help me?

Posted by Dave Hawley on May 29, 2001 2:48 AM

vData = Tabelle1.Range("A1:M50") oSheet.Range("A1:M50") = vData oBook.SaveAs Tabelle1.Range("D4") & ".xls" oExcel.Quit Löschen.LöschTab

Hi Marcus

You could do this manually we relative ease.
Right click on the Sheet name tab and select "Move or Copy"
Select "(new book)"
Tick "create a copy'
Click Ok.

Now delete the data you dont want and save the Workbook.

Or you could use a macro like this:

Sub TryThis()
Dim sName As String

sName = Tabelle1.Range("D4") & ".xls"

Tabelle1.Range("A1:M50").Copy

Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs sName
Application.Quit
End Sub

Dave

OzGrid Business Applications

Posted by Tuc on May 29, 2001 6:00 AM

vData = Tabelle1.Range("A1:M50") oSheet.Range("A1:M50") = vData oBook.SaveAs Tabelle1.Range("D4") & ".xls" oExcel.Quit Löschen.LöschTab
Markus,
I ran into the same type of problem when I was copying ranges within the same worksheet. I originally tried to do a direct copy the destination. This copied the formatting great, however it also copied the relative addresses to the target area. All I wanted was the values. I then tried copying to the clipboard and then pasting the values, but this didn't copy all the formatting correctly. The solution turned out require a combination of both. Copy to the destination to get the formatting, then copy the range to the clipboard and then paste to the target range 100 rows below the original document area.

' Copy the Document Area, This also copies the cell formats to the target range
rngNamedRange.Copy rngNamedRange.Offset(100, 50)

' Copy the Document Area to the Clipboard, to get the cell values
rngNamedRange.Copy

' Paste the Values copied to the Clipboard from the Document Area
rngNamedRange.Offset(100, 50).PasteSpecial xlPasteValues

I think this might work for you, even though you are copying to a different worksheet.

Let us know how it comes out.

Tuc

Posted by Tuc on May 29, 2001 6:03 AM

Additional Information

vData = Tabelle1.Range("A1:M50") oSheet.Range("A1:M50") = vData oBook.SaveAs Tabelle1.Range("D4") & ".xls" oExcel.Quit Löschen.LöschTab

I neglected to include that I had declared a variable and was offsetting the target area, down 100 rows, and over 50 columns.

Dim rngNamedRange as Range

Set rngNamedRange = activesheet.Range("A1:F50")

Tuc