Free Microsoft Excel 2013 Quick Reference

Moving Sheets To A Different Workbook XL2003


XL 2003, Windows XP.

I have a question regarding moving sheets to a different workbook.

Sub Blah()

Dim oActiveSheet As Object

blah blah blah

Sheets("Duplicate").Copy Before:=Sheets("Duplicate")
Set oActiveSheet = ActiveSheet


End Sub

On the last statement, I get a Run-time error '9': Subscript out of range.

Where did I go wrong? The other workbooks is named
"2006.05.30Presentation.xls". I want to move oActiveSheet from WorkbookA to
2006.05.30Presentation.xls at the very end.

Thank you.

Best regards,

Post your answer or comment

comments powered by Disqus
How do you copy a sheet from one workbook to a different workbook?

Sub Workordercopy()
ActiveSheet.Copy Before:=Workbooks("workorders.xls").Sheets(1)
End Sub


Can anyone tell me how to move/copy a worksheet to a new workbook and keep the values made by the formulas but not have the formulas included on the new sheet?

In our current method, we copy/move the sheet to a different workbook, sometimes 60 or more, then we must copy,paste special, values, in the columns that contained formulas.

Any help would be greatly appreciated


Hey everyone,

I'm brand new to this site and this is my first post. I'm trying to figure out how to do this and it will really help me with my job.

I know how to reference a specific cell in a different workbook. However, I have one workbook that is updated with new financial data each month and has a new sheet created (i.e. April 2012, May 2012, June 2012, etc). I'm trying to create a master sheet that will reference to a specific cell in the most recent sheet in a different workbook.

For example, I have 40 deals, Deal 1, Deal 2, Deal 3, etc all with their own workbooks and different sheets each month. So I'm creating a Master sheet that will reference the most recent data in each of these workbooks. Is this possible? If so, how?

If anyone could help with this, I'd be eternally grateful. Thanks in advance.

I was fortunate to get help on the listbox code but need to direct the selection to a different workbook than the one that contains the listbox. I'm not good enough to see what I'm doing wrong. Played around with different combos of selecting the workbook but can't get it to work. Thanks for the help. Amazing knowledge in this group.

Option Explicit
Dim Rw         As Long
Private Sub cmdEnter_Click()
Dim WS1 As Worksheet
Set WS1 = Workbook("dataform.foraddtest").Sheets("order")
    Rw = Me.ListBox1.ListIndex + 2
    With WS1
        'Goes to cell . .G5  G6  G7  I7  K7  D3  I3  B11 B12 B14 B15
        .Range("g5").Value = ActiveSheet.Cells(Rw, 1).Value
        .Range("g6").Value = ActiveSheet.Cells(Rw, 2).Value
There are several more lines of code but the issue seems to be on the workbook selection. Thank you!

hi all,
i was handed-over a VBA code which copies the 1st sheet of all workbooks in a folder to a different workbook.
there are a few minor changes i wanted on this code.

1) The folder also contains the workbook to which the other workbooks are to be copied. Quite obviously, we have to open the destination workbook and run the code to copy those files to this workbook. But what happens is, at the end an error message appears saying that the "destination" folder couldn't be opened. So i was trying to edit the code so that it wouldn't not try to open the 'destination" workbook.

2) After all sheets have been copied, i want the original sheet to be active one and not the sheet which have been copied last.

My code-
Option Explicit
Sub MergeData()
    Dim FSO         As Object '<-- FileSystemObject
    Dim fsoFol      As Object '<-- Folder
    Dim fsoFil      As Object '<-- File
    Dim WB          As Workbook
    Dim wks         As Worksheet
    Dim ShNames()   As String
    Dim Path        As String
    Dim PathNew     As String
    Dim i           As Long
    With ThisWorkbook
        Path = .Path & ""
        Application.ScreenUpdating = False
        If .Worksheets.Count > 1 Then
            ReDim ShNames(1 To .Worksheets.Count)
            For i = 1 To .Worksheets.Count
                ShNames(i) = .Worksheets(i).name
            .Worksheets.Add After:=.Worksheets(1), Type:=xlWorksheet
            Application.DisplayAlerts = False
            Application.DisplayAlerts = True
        End If
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set fsoFol = FSO.GetFolder(.Path & "")
        For Each fsoFil In fsoFol.Files
            If Mid(, InStrRev(, ".") + 1) Like "xls*" _
            And Not = .name Then
                Set WB = Workbooks.Open(fsoFil.Path, False)
                WB.Worksheets(1).Copy After:=.Worksheets(.Worksheets.Count)
                .Worksheets(.Worksheets.Count).name = _
                Left(Left(, InStrRev(, ".") - 1), 31)
                WB.Close False
            End If
        Application.DisplayAlerts = False
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        Set WB = Nothing
        If MsgBox("Save Me now?...", _
        vbQuestion Or vbYesNo Or vbDefaultButton1, _
        "You wanna save?") = vbYes Then
        End If
    End With

End Sub
thanks in advance.

I'm attempting to copy a worksheet to a different workbook, so that i could use the formulas for the data in the new workbook. But, the formulas still link back to it's original (Master)workbook instead.
I was hoping the formulas would just implement themselves nicely to work for the new workbook, is there a way to do this?


I have an active WB1.......
I can save sheet1(or any sheet) to a different WB (WB name:Carpet)........

Now The active file is WB2....
I want to save sheet1(or any sheet) to the WB(Carpet)

I know how to save a sheet to a different WB, but the problem is saving another sheet(from the same active file or another file) to the same WB

Could any u help me doing this job?


I need help in copying sheets to a new workbook. My present workbook has worksheets (that can vary from one to 14). I have obtained subtotals on the data on each of the sheets. I have written a macro to filter the data in each sheet to show the “total” rows. What I want to do is to copy visible cells only for all available sheets in the present workbook to sheets in new workbook. It is easy if the number of sheets in the original workbook is constant.

Can someone please help me with a macro for the above problem? Thank you very much.

Hi there.

I'm trying to create a formula that refers to a different workbook.

The workbook I need to refer to will change based on the info in the columns before it.

For example, consider a "master" workbook with several people down column 1 who each have their own "personal" workbooks saved with their own name in the title i.e. the file name will be C:Documents and SettingsdesktopMrExcel.xls

DateTime PlaceMr ExcelMr Word

The date, time and place for Mr Excel are calculated with a reference to Mr Excel's personal workbook. I need a formula that will automatically lookup the correct workbook based on whoever's name is in column 1.

i.e. =vlookup(A2,"C:Documents and Settingsdesktop[insert name of whoever is in A2],A:I,9,false).

This will save me from changing the formula for every single person down column A.

Very grateful for any suggestions!


I am trying to replace a worksheet name in a formula linked to a different workbook using Excel 2010.

Current: ='M:ACCOUNTINGMike2010 Sales6 - Period 20106152010[3 - BoulderWE061510.xls]Weekly Sales'!$E$46

Need to Replace with:='M:ACCOUNTINGMike2010 Sales7 - Period 20106222010[3 - BoulderWE062210.xls]Weekly Sales'!$E$46

ok so here is my issue. Using Excel 2003 and/or 2007 I simply would use Replace 0615 with 0622 I would have to hit esc and it would give me a #REF! error. Then I would replace 6 - Period with 7 - Period and presto it was updated. Now using Excel 2010 when I try to esc it stops.


Hi there

I use this code to export sheets to a new workbook.

Sub ExportSheet()
Dim WB As Workbook
Dim FN As Variant
Worksheets(array("Sheet1", "Sheet2")).Copy
Set WB = ActiveWorkbook
FN = Application.GetSaveAsFilename( _
FileFilter:="Excel Files (*.xla), *.xla", _
Title:="Specify Location for Copy:")
If FN = False Then
WB.Close Savechanges:=False
WB.SaveAs Filename:=FN
WB.Close Savechanges:=False
End If
End Sub
How ever, the exported sheets transport all properties and links existing in some cells. What I would like to know:

Is it possible to export those sheets and delete or convert existing links and some formulas?

Thank you all

I would like to know if there is a macro to copy worksheets (input, and parameter sheets) to a new workbook and save it, see attached file. I want to save it to a path given on the start tab. Also the name of the new workbook is also given in the start tab see attached file. I am would like to know if this is possible. Thanks

I'm trying to copy a sheet to a new workbook and save it by using a macro. I found this code ( which works all and well, except when it runs into a sheet that uses a macro (a button or a cell selection change).

Basically, I need to copy paste all the values without pasting any macros used (in order to upload the document correctly, it is required to not have any VBA within it).

Does anyone know how to do this?


Excel crashes everytime I copy sheet to a new workbook... does anyone know how I can stop this>?

Can anyone help me.

I have a workbook with client worksheets in them. When a clients case is completed i need to move the sheet into another workbook called 'archive'

I need a macro to move a sheet (selected from a list) to the archive workbook which will be closed - so the macro i think will need to open it.

Any help greatly appreciated.

I am using the following code to move cell values to a different worksheet. I works fine except that the cell comments are lost. Is it possible to pickup the comments as well?

 'copy the data from the current row and delete
iRowA = Range("A" & (ActiveCell.Row)).Value 
sRowj = Range("J" & (ActiveCell.Row)).Value 
dRowL = Range("L" & (ActiveCell.Row)).Value 
dRowM = Range("M" & (ActiveCell.Row)).Value 
dRowN = Range("N" & (ActiveCell.Row)).Value 
 'Activate Destination and find first free row
 'Paste data if not empty and calculate
Range("A" & (ActiveCell.Row)).Value = iRowA 
If sRowj  "" Then Range("J" & (ActiveCell.Row)).Value = sRowj 
If dRowL  0 Then Range("L" & (ActiveCell.Row)).Value = dRowL 
If dRowM  0 Then Range("M" & (ActiveCell.Row)).Value = dRowM 
If dRowN  0 Then Range("N" & (ActiveCell.Row)).Value = dRowN 
Range("I" & (ActiveCell.Row)).Value = "p" 

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

Hello There,

I am trying to alter data in another workbook within vba. I know if the code was local I could use sheet1.range("a1").select but I have the code running from a different workbook. Currently I am using


But the problem with this is that if the user changes the name of the sheet then the code will not work. Can I still reference VBA name of the sheet instead of the excel name?



I have a workbook that contains multiple sheets where we enter data on a weekly basis.
The columns are the same in all the sheets. However, the rows containing data will be different in the sheets.
For example, the first sheet may have data in 10 rows, second in 5 rows and so on. This can keep increasing as we keep entering data
I would like to consolidate all the multiple sheets into a different workbook- Master Workbook that would contain the rows containing data from each of the sheets.
Once I have copied for the first time into the Master Workbook, would like to just keep updating the 'Master' workbook as and when data is entered in the original workbook.

Please advice.

I am using 03 Excel and here is what I am trying to achieve. I have 10 "active" rows where data is being tracked and updated. When the user marks the an "active" row as complete by putting an "A" in a particular cell I would like to cut this data and move it to a separate sheet to make room for a new "active" row. And if the user puts a "B" in a particular cell that "active" row is now dead and I want to move it to another part of the spreadsheet used for tracking history. So...

1. If there is an "A" in cell G3 then move entire row to the first available row on sheet 2.

2. if there is a "B" in cell G3 then move entire row to the bottom of the list on the current sheet.

I would like the statement to loop through the 10 "active" rows for an "A" or "B" and perform the actions above.

I have been trying for a week now to get this to work but cant seem to do it with my limited code knowledge. Can anyone lend me a hand?

Thanks in advance!

I am using Excel 7 and 10. Is there an easy way to copy an existing sheet in a workbook to another existing workbook.

I seem to be having a lot of questions as of late... but you folks have been most helpful.

What I'm attempting to do here, is to include a button on a spreadsheet that when pushed, will move the open sheet to a different sheet within the same spreadsheet.

(i.e. I have 2 tabs, "One" and "Two", and when I push the button in sheet "One", it will open the sheet "Two")

I'm working under the impression this is Macro-related...and it's been roughly 6 years since I've dealt with them, and consequently, forgotten everything it seems.

Any help or examples you could offer would be appreciated.

I am trying to copy selected cells in one workbook to a specific workbook (R2008a.xls), living in the same directory.
this is what I am using with no luck:

Sub AddToFailed()

Selection.Copy Workbooks("R2008a.xls").Sheets("R2008a").Range("A" & _
Sheets("R2008a").Range("B65536").End(xlUp).Row + 1)
Application.CutCopyMode = False

End Sub


guidance will be much appricieted


in excel 2003 how do you mirror a workbook to a different work. I know how to mirror different cells, i want to do a complete workboook including tabs,

thanks in advance

Hi All,
I would like to know how to make the vba code to change the current active worksheet to a different worksheet.
For example, I have three sheets.
1. Summary
2. Product1
3. Product2

I am going to write my vba code on Summary worksheet. But I want the code to work on worksheet "Product1". Just wanted to avoid prefixing ActiveSheet everytime. I have a sequence of operations that needs to be done on the "product1" worksheet and return back to doing further tasks in "Summary" worksheet.


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