Simple Copy Paste Macro

**NavSingh** · 06-21-2007, 12:54 AM

I have a really large excel file (90 Megs) And i want to copy the values and formats of certain sheets to another workbook. Ive tried using a copy/paste macro i had from work but the sheets have alot of groups and info so it kept freezing

Can anyone help me?

**royUK** · 06-21-2007, 03:46 AM

I would create a template for ease -see this

failing that why not simply use the copy the whole sheet?

**NavSingh** · 06-21-2007, 06:39 PM

Its for work, so i need to run this every week and one copy paste macro would help. The other one we used to use doesnt work, i dont know why but if anyone can just paste some code that would copy specific worksheets' values and formats into a new book

**NavSingh** · 06-21-2007, 11:38 PM

anyone? ? ?

**royUK** · 06-22-2007, 02:26 AM

Are you copying values or formulas? Which sheets need copying.

You could either save the existing workbook as a new name & delete any unnecessary sheets or as I said copy specific sheets to a new workbook.

For example

Option Explicit

Sub makeNew()
    Dim sNewName As String
    'get a new name for workboo, tis could be generated from the date perhaps
    sNewName = "newbook"
    'save existing workbook as the new name
    ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & sNewName & ".xls", FileFormat:= _
                        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
                                                                                           , CreateBackup:=False
    'prevent warning message when deleting sheets
    Application.DisplayAlerts = False
    'remove unwanted sheets
    ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2")).Delete
    Application.DisplayAlerts = True

End Sub

**NavSingh** · 06-24-2007, 11:16 PM

I still have this problem, I need to copy like 10 sheets To a new workbook. I don't want to save the work sheet because that will take too much time. I just want to copy Values and formats to a new work book for specific sheets.

ive tried this

Sheets(Array("Sheet1", "Sheet2")).Select
Sheets("Sheet1").Activate
Sheets(Array("Sheet1", "Sheet2")).Copy
application.CutCopyMode = False

Sheets("Sheet1").Select
Cells.Select
selection.Copy
range("D1").Activate
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
range("A1").Select
application.CutCopyMode = False

Dim SaveDate
SaveDate = Date$

ActiveWorkbook.SaveAs Filename:="D:Workbook " & SaveDate & ".xls" _
, FileFormat:=xlNormal, password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

range("A17").Select

The problem with this is, the computer I do it on is too slow, so it just freezes up because of the array at the top. how can i do each sheet at a time??

Simple Copy Paste Macro

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Simple Copy Paste Macro

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions