Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 5th, 2004, 02:45 PM
Authorized User
 
Join Date: Jun 2003
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Copy a worksheet range to a new workbook

I am trying to programatically copy the contents of one spreadsheet into another workbook. I can get as far as dynamically creating the new workbook and use only one worksheet in the new workbook but can't find the best solution to transferring the contents and formats for the Range("A1:L67").
Any hints would be appreciated! Thank You!

CEJ
__________________
CEJ
 
Old May 6th, 2004, 03:02 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Most of this code is made by the MacroRecorder:

Sub NewWbkTransData()

Dim btn, rght As Long
Dim NewWbk As String

NewWbk = "SomeName"

' Add a new workbook
    Application.Workbooks.Add
    Range("A1").Select
' Rename the workbook as you cant be sure which name Excel uses
    ActiveWorkbook.SaveAs Filename:="C:\" & NewWbk & ".xls"

    Windows("OldWbk").Activate
    Sheets("Sheet1").Activate

' Either - determine the active range...
    Range("A65000").End(xlUp).Select
    btn = ActiveCell.Row

    Range("IV1").End(xlToLeft).Activate
    rght = ActiveCell.Column

    Range(Cells(1, 1), Cells(btn, rght)).Select

' .. or select it if you know the range beforehand
' Range("A1:L67").Select

' Copy the selected range...
    Selection.Copy
    Workbooks(NewWbk).Sheets("Sheet1").Activate

' and Paste it into the new workbook
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

End Sub

The OldWbk is, of cause, the one you run the macro from. See if you can use it.
 
Old March 14th, 2006, 11:55 PM
Registered User
 
Join Date: Mar 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

I had seen a code in the forum for copying one worksheet to an entirely new workbook in vb. Can anyone give me the code in C#?
Eventhough I tried to put the same code in C#, I'm getting millions of errors. Any help in this regard will be great.

Thanks
Raja Raman






Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy worksheet to another workbook and rename it Probleminfinity Excel VBA 1 August 19th, 2007 11:38 PM
Copy Worksheet into another workbook money Excel VBA 4 March 8th, 2007 05:17 AM
Searching a range in a worksheet tiyyob Excel VBA 2 January 13th, 2006 11:26 PM
Copy workbook contents into new one crmpicco Excel VBA 0 May 17th, 2005 05:10 AM
Find Unknown Worksheet Names in an Excel Workbook bcarmen VB.NET 2002/2003 Basics 1 March 3rd, 2005 12:20 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.