Free Microsoft Excel 2013 Quick Reference

Excel 2007 copy/move worksheet to another workbook

If I copy or move a worksheet with a table and graphs to another workbook, it
keeps the links to the original workbook and the graphs show wrong, i.e. the
x-axis loses its values. In Excel 2003 this worked fine. Somebody has some
idea or is it a bug.

Post your answer or comment

comments powered by Disqus
I am currently using Excel 2003, I have a 3.11mb file with about 20
worksheets, this file has been around much longer than I have. Every month I
would copy 4 worksheets to another workbook. This month I am no longer able
to do this, I right click the tab, and choose move or copy, and choose the
workbook, and check create a copy, nothing will happen, it does not matter if
I right click on the worksheet tab or use the Edit menu. It will let me move
the worksheet, but it will not allow me to copy it. I got an error message
on named ranges (saying they already exist) when I moved it to a new workbook
and then tried to copy it back to the orginal, I looked at the name ranges,
and there had to be over 50 of them, I don't need them, so I deleted them
all, expect one. it will not allow me to delete it, and it is refering to a
workbook that no longer exist, before the name of the range it has to square
boxes, I can not rename or deleted it, I dont know if this has anything to
do with not being to copy the workbook.
Please help if you can

I have a workbook with a worksheet that referances other worksheets within
the same workbook. When I copy this worksheet to another workbook, it still
referances the old workbook....instead of referancing the new workbook. The
worksheets have the same name. How do I make it referance the new workbook?

hi, I am trying to copy all worksheet to another workbook, but cant find the mistake in the code.

wb1 = ActiveWorkbooks 
wbt = "C:Tx Monitoring 2011.xls" 
Application.Workbooks.Open (wbt) 
wb1.Sheets.Copy After:=Workbooks("Tx Monitoring 2011.xls").Worksheets(Workbooks("Tx Monitoring 2011.xls").Worksheets.Count)
[COLOR=red]----> vba tells me there Is an error here. Object required[/COLOR] 
 'paste the copied data in there
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
:=False, Transpose:=False 
Workbooks("Tx Monitoring 2011.xls").Activate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have a basic question that I cannot figure out. I am using Excel 2007 and want to copy a worksheet (saved as an Excel 97-2003 file) to another workbook (also saved as an Excel 97-2003 file). When I go through the steps to copy it, I get an error message that says,

"Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination work book, you can select the data, and then use the Copy and Paste commands to insert it into the sheets of another workbook.

I need to keep this as an Excel 97-2003 file because files are shared across our company and about 1/2 of the people are still using Excel 2000. I can copy and paste the data manually, but I lose my formatting (even with the "Paste Special" option). It's a pain to have to do this every week when this report is due. I was wondering if someone had any ideas. I didn't have this problem when I was running Excel 2000.

One last piece of information. I run a Excel 2007 macro to manipulate the data on the worksheet before it is saved in a 97-2003 format. Could that have anything to do with it?

Thanks in advance for any help,

Chuck Norman

Hello vba guru's, I'm trying to add at the end of a vba macro to take the active worksheet and then copy it, then add it to another workbook. I've tried a couple of things and I searched but I just can't get it completely. Below is a portion of vba code that goes through a worksheet and formats it, with header/footers, column headings...ect. Some of which I recieved help from this site and others I recorded and modified...but this one last step is failing me?!

I tried this:

With Workbooks("Closed District Lead Summary Jan to Mar 2011.xls")
        .Sheets(NewSh).Copy _
            Before:=Workbooks("K:PROJNexTouch2010 ReportsVersions" & "" & "District Lead
Summary " & Format(Now, "yyyy-mm-dd") & ".xls").Sheets(1)
End With
And this...

Thanks for looking!

By the way, the 'other workbook' is a workbook that is already created, I want to add the one sheet or tab to this already created workbook. I did find some code from Lieth Roth but that code was creating a new workbook. Here's the code I found in this formn...perhaps this can be modified for my needs?

sub move()
Dim FileSpec As String
Dim Ia As Integer
Dim NewWkb As Workbook
Dim Wksa As Worksheet
Dim WksName As String

With ThisWorkbook.Worksheets(NewSh) '("Replicator")
FileSpec = .Range("A1")
FileSpec = IIf(Right(FileSpec, 1) <> "", FileSpec & "", FileSpec)
FileSpec = FileSpec & .Range("A2")
For Ia = 1 To .Range("A3")
WksName = .Cells(I + 3, "A")
Set Wksa = ThisWorkbook.Worksheets(WksName)
If NewWkb Is Nothing Then
Set NewWkb = ActiveWorkbook
Wksa.Move After:=NewWkb.Worksheets(NewWkb.Worksheets.Count)
End If
Next Ia

NewWkb.SaveAs fn 'FileSpec
NewWkb.Close SaveChanges:=False
End With
End sub

UPDATED - - - I think I found the answer, I record the 'copy' via the normal fashion then added that recored code to the existing code....first test seemed to work well....

    Sheets(NewSh).Copy After:=Workbooks( _
        "District Lead Summary 2011-03-17.xls").Sheets(1)


Currently using excel 2007. This is the first time i encounter this type of problem. I tried to copy a worksheet to a new workbook but failed. Anyone know how to resolve this problem?


Hello everyone! Happy Friday!

Can anyone please help me make this code to work. All I want is to copy a work sheet to another workbook. Kinda like having a clone copy of the sheet to another workbook either by a button click or open event.

    Dim WS1 As Worksheet, WS2 As Worksheet 
    Set WS1 = Workbooks("C:UsersDesktopData ExtractionDataDump.xlsm").Sheets("Sheet2") 
    Set WS2 = Workbooks("C:UsersDesktopData ExtractionReport.xlsm").Sheets("Sheet2") 
    WS1.UsedRange.Copy WS2.Cells(Rows.Count, "A").End(xlUp) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
PLease help! thanks in advance


Using Excel 2007 on Win XP.

I use VBA to copy several worksheets from one .xlsm workbook to an new .xlsx workbook. In the new workbook, 4 cells in one of the worksheets end up with font size = 8, whereas the source worksheet cells are font size = 10. No other worksheets appear to have an issue.

I can / will code to address the fontsize of these cells after the copy , however I don't understand why it is occuring / how to prevent it from occuring.

The worksheet with the issue is "Scorecard_2". the cells with the error are D7, D9, D10 & D11

     'stephen mcintyre May 2011
     'copy report worksheets to new workbook in preparation for distribution
     'Delete the named range "FinCal" from the new workbook
    Dim wb As Workbook 
    Dim NewBook As Workbook 
    Dim icountsheets As Integer 
    Dim icounter1 As Integer 
    Application.ScreenUpdating = False 
     'force saveas format
    Application.DefaultSaveFormat = xlExcel12 
    Set wb = ThisWorkbook 
    Set NewBook = Workbooks.Add 
    icountsheets = NewBook.Worksheets.Count 
    wb.Sheets(Array("Trading_Report", "Priority1s", "StoreDownTimeDetailNew", _ 
    "Scorecard_1", "Scorecard_2", "Scorecard_3", "NonScansDetail", "ReinstatedSales", _ 
    "ChangeManagement")).Copy after:=NewBook.Sheets(icountsheets) 
    Application.DisplayAlerts = False 
     'delete named range not requried in new book
     'delete worksheets created by workbook.add
    For icounter1 = icountsheets To 1 Step -1 
    Next icounter1 
    Application.DisplayAlerts = True 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
In my Excel 2007 install the default font size when creating a new workbook is = 8. If I change the default fontsize to be 10 the issue doesn't occur.

Copying the workbook via the menu options (ie not using VBA) results in two outcomes depending on the method:
1. Creating a new workbook first & then coping the worksheet results in the same issue
2. Copying the worksheet to a new workbook (& letting Excel create the workbook on the fly) doesn't result in the issue

Updating the VBA to only copy "Scorecard_2" doesn't change the outcome

I have also reapplied formatting to the source worksheet with no discernable change in outcome

Appreciate your assistance,


Hi VBA Guru's

I've been looking all day for Copy Active worksheet to all workbooks in folder. I need to copy my sheet "Data Overview" to all workbooks in C:/Asia, I'm using excel 2007 I tried this code below but it says it can't open the file error 1004

Sub UpdateAllWorkbooks()

  Dim MyFolder As String
  Dim SrcWks As Worksheet
  Dim Wkb As Workbook
  Dim WkbName As String
    Set SrcWks = ThisWorkbook.Worksheets("Data Overview")
    MyFolder = "C:Asia"
      WkbName = Dir(MyFolder & "*.xlsx")
        Do While WkbName <> ""
          Set Wkb = Workbooks.Open(WkbName)
            SrcWks.Copy Before:=Wkb.Worksheets("Sheet1")
            Wkb.Close SaveChanges:=True
          WkbName = Dir()
End Sub
Thanks in advance

I created a worksheet/spreadsheet with functions and references to cells in other worksheets/spreadsheets within same workbook to create a report of other worksheets within the same workbook.

I have lots of workbooks/spreadsheets on which I have to copy this worksheet lots of workbook, but I when I copy the worksheet to another workbook, excel create references to the workbook where I copied from.

Does anyone know how to avoid excel doing this?

Any help would be very much appreciated.


vba coping worksheet to another workbook and renaming...

i can copy the sheet ok, but i would like to rename the sheet in the copied to workbook as well. (here's the code that copies...)

Set ws = Worksheets("Materialssheet")
Set wb = Application.Workbooks.Open("3-serverdatadatabaseMaterialListings.xls")
If Err.Number 0 Then
MsgBox Err.Description & ""
mytabname = ActiveSheet.Range("a3").Value
ws.Copy after:=wb.Sheets(wb.Sheets.Count)

thank you...!

I am trying tomove (or copy) one worksheet to another book, but when I right
click on the worksheet i want to move, the second book does not appear in the
"to book:" pull down.

BTW, the second book is open as well.

Microsoft help says they have to be on the same instance, no clue what that

Any ideas?


I am using VBA to copy and array of sheets to another workbook. I want to use Copy Before, however after the sheets have been selected the macro bombs out at Sheets.Copy before:=. Can somebody help?

My code is as follows:

Dim Arr() As String
Dim I As Integer
ReDim Arr(Worksheets.Count)
Arr(0) = Worksheets.Count
For I = 1 To Worksheets.Count
Arr(I) = Worksheets(I).Name
Next I
For I = 1 To Arr(0)

ActiveSheets.copy Before:=Windows("xxx.xls").Sheets(16)

Next I


I want to copy old worksheet from old.xls (screen 1) to new book new.xls
(screen 2 ) on an extended desktop.
Screen 1 containing old.xls file in originally opened excel.
Screen 2 has new file new.xls in newly opened excel.
Dragging over is unsuccesful.
So is Edit-Move or Copy to new.xls.
There must be a way!!

Hi Everyone,
What I need to do is programatically copy a worksheet from a workbook (call it
Book1) to another (call it Book2), renaming the Worksheet before it goes into
Book2. The initial condition being that Book2 is closed. I thought I had a way
but I keep running into various problems. I'm not reprinting that here because I
think I need to start fresh. My question is, what is the cleanest way to do it?
Thank You all so much in advance,

I keep hitting "Esc", but I'm still here!

According to Excel Help, I should be able to copy a chart/graph to another
workbook. However, when I try, the Paste option is not highlighted as a
choice. Does anyone have any ideas how I can accomplish this copy/paste

Thank you.

I need help copying an existing sheet along with the links on that
sheet to another workbook. I have two workbooks, say Workbook1 and
Workbook2. They are the same exact workbooks with the same worksheets
in them, the same layout. Only the data is different. In workbook 1 I
have inserted a sheet that summarizes the data from the other sheets in
that workbook. Now I want to copy that sheet to Workbook2 so that I
don't have to make a summary page for Workbook2.

The problem is that the sheet has links in it and when I copy and/or
move the sheet to the new workbook those links are still linked to the
old workbook.

Is there a function or command that I am supposed to use when doing
this so that the links are dynamic to the workbook it is being copied

Thanks for the help.
Steve Monczka

I've been searching around the net for a few hours now, but cant seem to find a solution.

The thing is, I need a vba code for excel that copies a worksheet from one workbook to another.

I have workbook Art.xlsx
And a workbook test.xlsx
In the Workbook test is a sheet called Test1

When you press a button in Art.xlsx, the sheet test1 from workbook test.xlsx needs to be copied and pasted next to a sheet called materials in Art.xlsx

Can anyone help me out?


I currently have a workbook that I use for generating invoices.

The workbook consists of a number of worksheets and all the pricing data goes into sheets called ssrSheets.

The user has the ability to add as many ssrSheets as he wants with a macro.

Each time a user wants to create an invoice he opens up a template of this workbook and modifies to create an invoice for his requirement, add the number of ssrSheets he wants what he wants to call all the items etc

All the quantities, hours and prices etc. go into each ssrSheet, each workbook could have any number of ssrSheets depending on the users requirement. However the format of the ssrSheets are always the same, only the input the user puts in is different.

Sometimes we may have invoices for a particular item in an ssrSheet in another workbook, so rather than the user typing in all the inputs into the ssr Sheet again, I would like some help on writing a macro that will copy over all the user data from the ssr Sheet in the other workbook into the ssr Sheet in the current work book.

Regardless of the workbook name, the ssr sheets will always have the name SSR_Sht1 , SSR_Sht2 , SSR_Sht3 etc... depending on how many sheets there are.

I would like the macro to work, such that the user opens the workbook he wants to copy from and is then asked by a userinput box which ssr Sheets he would like to copy into his new workbook.

Lets say I have opened up a new template with 20 ssr Sheets in it, I am currently in SSR_Sht 1 and I am pricing something that I have priced before, so I run the macro and I open up the workbook the that the previously priced item is in, I am then asked which ssr Sheet I would like to copy into SSR_Sht 1 , I type in the SSR_Sht number and then the data from that sheet is automatically copies over.

The following data will need to be copied only -
For Labour -

Column A, C, D, E, F, H

For Every other sub heading

Column A , C, D, E , G, H

I am sorry for the long winded explanation, but I wanted to give as much data as possible.

Attached is a copy of the ssr sheet format, on some sheets users may have added more rows under the sections and I want this to be copied over as well.

Thanks in advance.

Hi all,

I have a macro that I recorded then modified slightly. This is as follows:

Sub Copy_Worksheet() 
    ActiveSheet.Copy After:=Workbooks("Filename_Sample.xlsx").Sheets(1) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So most of it is fine but I would like the copied worksheet to be placed at the end of all the worksheets already in the Filename_Sample file. The Filename_Sample workbook will be my master, so it will have a growing number of worksheets as I do this process. I don't know how to do this and the macro recorder is not useful for this either.

Could someone please help me as I have to do this for 500+ ws.

Thank you so much.

Hi All, i am a beginnner in macro.
I need to come up with a macro code to copy 2 worksheet from 2 workbooks to my current workbook. I do not know what is the code such that i can copy different worksheets from other workbooks to a currently opened workbook.

Below is the scenerio i want to achieve:

worksheet1 from workbook1 need to be copied and paste as a worksheet in workbook3.
worksheet1 from workbook2 need to be copied and paste as a worksheet in workbook3.
workbooks will eventually contain 2 worksheets which are from workbook1 & 2.

PLs help. Thank

I have a worksheet with a macro that copies the worksheet into another workbook and renames it in that workbook. The only problem is, there's worksheet-name specific code in the macros of that worksheet (Worksheet_Calculate and Worksheet_Change events) that make the copy-macro crash. I've been fighting with it for too long and I was wondering if there was some way I could copy the worksheet over to the new workbook without the VBA attached?

I'd like to copy an embedded Excel chart from one worksheet to another and position it on the target worksheet.

I copy cell data like this:
Worksheets("Input Worksheet").Range("A1:J55").Copy Destination:=NewSheet.Range("A1")

How can I copy charts?

Todd Geiser

I simply am trying to copy a formula from a sheet in one workbook (Ex:
"Main" sheet from workbook "WB1") to another workbook (Ex: WB2). Unlike some
of the other posts about copying formulas, I want Excel to include the
reference to the original workbook. I thought I've done this before, but for
whatever reason, I can't seem to get it to work. The formula I want to copy
is an array-based formula (though I don't think this would be the reason why
this isn't working). I've even tried copying a non-array-based formula & the
same thing is happening. Here's my array-entered formula that I'm trying to
=AVERAGE(IF(($J$17:$J$1500=$AQ$12)*(TEXT($I$17:$I$ 1500,"mmmyyyy")=TEXT(AD$2,"mmmyyyy")),$F$17:$F$150 0))

Shouldn't simply copying & pasting the formula from 1 workbook to the other
include the reference to the original workbook? If not, where do I need to
put the references to the other workbook w/in the formula?

No luck finding an answer? You could always try Google.