Go Premium for a chance to win a PS4. Enter to Win


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
  • 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.
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

877 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