Solved

excel 2010 copy

Posted on 2014-03-21
8
343 Views
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?
0
Comment
Question by:JeffBeall
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 7

Assisted Solution

by:COACHMAN99
COACHMAN99 earned 167 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.
0
 
LVL 5

Accepted Solution

by:
dani gammon earned 167 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.
0
 
LVL 1

Author Comment

by:JeffBeall
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.
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 166 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", _
                                        bSelectedOnly:=false
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
Next

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
    ActiveWorkbook.Sheets(strSummary).Delete
    shtDone.Name = strSummary
    Err.Clear
End If

firstSheet = True
lLastRow = 1

If Len(sAdditionalCells) > 0 Then
    Set rgAddtlCells = range(sAdditionalCells)
    If Err <> 0 Then
        Set rgAddtlCells = Nothing
        Err.Clear
    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
            Err.Clear
        End If
    Else
        If lLastCol > 0 Then
            Set rgCellsToInclude = shtLoop.range(sFirstCell, _
                    shtLoop.Cells(Rows.Count, lLastCol).End(xlUp).Offset(, lLastColOffset))
        Else
            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

    Else
        
        With rgCellsToInclude
            .Copy
            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

nxtSht:
Next lLoop

Set shtConsolidateSheets = shtDone
End Function

Open in new window

0
 
LVL 1

Author Comment

by:JeffBeall
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
0
 
LVL 5

Expert Comment

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

Author Closing Comment

by:JeffBeall
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.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39946062
Indeed, VBA Visual Basic for Applications.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

792 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