Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


excel 2010 copy

Posted on 2014-03-21
Medium Priority
Last Modified: 2014-03-21
i have an excel document with multiple sheet and it is opening slowly. So I want to copy just the data from all the sheets to a new excel document.
I tried selecting a sheet, highlighting everything, and then copying to a new excel document, and that works, however, i was hoping there is a faster way since i have LOTS of sheets.
is there some way to do this?
Question by:JeffBeall
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1

Assisted Solution

COACHMAN99 earned 668 total points
ID: 39945939
if you hold down the ctrl key and click each sheet with the mouse you can select all in one session.

Accepted Solution

dani gammon earned 668 total points
ID: 39945952
Right Click the Tab of the sheet you want to copy
Select -> "Move or Copy"
highlight which "worksheet" you want to copy
In the "Move selected sheets to book" section, choose "new book"
Check the box that says "Create a Copy"
Select "Ok"

Excel will open a new workbook with a copy of that worksheet.

You can then save the new workbook with a new name then copy over all the rest of your worksheets.

You could also just create a duplicate of the workbook by right-clicking on the file, selecting copy and then paste.

Author Comment

ID: 39945957
Ok, I selected all of the sheets, and tried

Ctrl C

but on the new excel document, when i do

Ctrl V

I just copies over one cell.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 39

Assisted Solution

nutsch earned 664 total points
ID: 39945959
Do you want all the sheets moved? You can use coachman99's shortcut or right-click the tab and pick Select All.

Do you want all the sheets consolidated in one that you can move over? Use the below macro and adjust the top parameters:

Sub ConsolidateSheets()
Dim sht As Worksheet, shtDone As Worksheet

Set shtDone = shtConsolidateSheets(bolTitles:=True, _
                                        strSummary:="All", _
                                        bolTab:=False, _
                                        strTabTitle:="", _
                                        sFirstCell:="A1", _
End Sub

Function shtConsolidateSheets(Optional sRangeToInclude As String = "", _
                            Optional bolTitles As Boolean = True, _
                            Optional strSummary As String = "All", _
                            Optional bolTab As Boolean = False, _
                            Optional strTabTitle As String = "BU", _
                            Optional sFirstCell As String = "A1", _
                            Optional bSelectedOnly As Boolean = False, _
                            Optional sAdditionalCells As String = "", _
                            Optional lLastCol As Long = 0, _
                            Optional lLastColOffset As Long = 0) As Worksheet

Dim shtDone As Worksheet, lLastRow As Long, rgLoop As range, rgCellsToInclude As range
Dim shtLoop As Worksheet, firstSheet As Boolean, lColOffset As Long, lColLoop As Long
Dim lgTabCol As Long, lIncrement As Long, sSelectedSheets As String
Dim arrSheets As Variant, lLoop As Long, rgAddtlCells As range
'requires TurnOffAllUpdates macro

'Const bolTitles As Boolean = True 'True if sheets have titles, false if they don't
'Const strSummary As String = "All" ' update to the name of the consolidated destination
'Const bolTab As Boolean = True 'get data from tab name ? True / False
'Const strTabTitle As String = "BU" 'title of column from tab name if bolTab=true
'Const sFirstCell As String = "B6" 'define first cell of data to copy (based on current region)

For Each shtLoop In IIf(bSelectedOnly, ActiveWindow.SelectedSheets, ActiveWorkbook.Worksheets)
    sSelectedSheets = sSelectedSheets & "\" & shtLoop.Name

sSelectedSheets = Mid(sSelectedSheets, 2)
arrSheets = Split(sSelectedSheets, "\")

Set shtDone = Sheets.Add(Count:=1)

On Error Resume Next
shtDone.Name = strSummary

If Err.Number <> 0 Then
    shtDone.Name = strSummary
End If

firstSheet = True
lLastRow = 1

If Len(sAdditionalCells) > 0 Then
    Set rgAddtlCells = range(sAdditionalCells)
    If Err <> 0 Then
        Set rgAddtlCells = Nothing
    End If
End If

For lLoop = LBound(arrSheets) To UBound(arrSheets)

    Set shtLoop = Sheets(arrSheets(lLoop))
    'determine what range to send
    If Len(sRangeToInclude) > 0 Then
        Set rgCellsToInclude = shtLoop.range(sRangeToInclude)
        If Err <> 0 Then
            Set rgCellsToInclude = shtLoop.range(sFirstCell).CurrentRegion
        End If
        If lLastCol > 0 Then
            Set rgCellsToInclude = shtLoop.range(sFirstCell, _
                    shtLoop.Cells(Rows.Count, lLastCol).End(xlUp).Offset(, lLastColOffset))
            Set rgCellsToInclude = shtLoop.range(sFirstCell).CurrentRegion
        End If
    End If
    If shtLoop.Name = strSummary Then GoTo nxtSht

    If bolTitles = True And firstSheet = False Then
        With rgCellsToInclude
            .Offset(1).Resize(.Rows.Count - 1).Copy
            lIncrement = .Rows.Count - 1
        End With
        With shtDone.Cells(lLastRow + 1, 1 + lColOffset)
            .PasteSpecial Paste:=xlValues
            .PasteSpecial Paste:=xlFormats
        End With

        With rgCellsToInclude
            lIncrement = .Rows.Count
        End With
        With shtDone.Cells(lLastRow + IIf(bolTitles, 1, 0), 1 + lColOffset)
            .PasteSpecial Paste:=xlValues
            .PasteSpecial Paste:=xlFormats
        End With
        If bolTab = True And firstSheet = True Then
            shtDone.Cells(2, lColOffset + 1) = strTabTitle
            shtDone.Cells(lLastRow, lColLoop).Resize(lIncrement) = shtLoop.Name
        End If
    End If
    If bolTab = True Then
        shtDone.Cells(lLastRow, rgCellsToInclude.Columns.Count + 1).Resize(rgCellsToInclude.Rows.Count) = shtLoop.Name
    End If
    If Not rgAddtlCells Is Nothing Then
        lColLoop = rgCellsToInclude.Columns.Count + IIf(bolTab, 2, 1)
        For Each rgLoop In shtLoop.range(sAdditionalCells).Cells
            shtDone.Cells(lLastRow + 1, lColLoop).Resize(lIncrement) = rgLoop.Value
            lColLoop = lColLoop + 1
        Next rgLoop
    End If
    firstSheet = False
    lLastRow = lLastRow + lIncrement

Next lLoop

Set shtConsolidateSheets = shtDone
End Function

Open in new window


Author Comment

ID: 39945969
dani gammon - that is what works! thank you. I also found that some of my sheets have tables inserted and it won't copy those sheets. However, that makes it only about 4 sheets to manually copy

Expert Comment

by:dani gammon
ID: 39945980
sweet! glad it worked! I use that all the time. :)

Author Closing Comment

ID: 39946031
Wow nutsch, that looks like a fancy script, is it Visual Basic?
sorry, I thought I would just use the right click move thing.
thank you for all the help.
LVL 39

Expert Comment

ID: 39946062
Indeed, VBA Visual Basic for Applications.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question