Discussion on:

Quickly move data to another workbook without copy and paste

my tip:
in the folder where you keep your file copy and paste (you can click on the file, press ctrl+c than ctrl+v) and you'll have a new file called "Copy of...... .xls"

Now just rename the file (if you want to), amend it and there you go. No menus, no additional clicking, selecting ect..

Works for me I guess it depends what you are used to and what you like. I like my solution

isn't it faster?

I gues there are 4 options which depends on situation:
file is opened or You're looking at it in folder.

if opened:
You can copy/paste selected cells (mouse or keyboard shortcuts)
copy/move entire sheet, to the same or new book(file)
use Save As - this one is usefull if You're looking at opened mail attachment, just Save As it and You already have a copy.

in case You're looking ant file in folder, there are no faster way then hitting ctrl+c, ctrl+v

anyway... all the methods are good, as long You are using any of them.

I like keyboard shortcuts, but it is also fast and easy to hit ctrl while slightly dragging the file with the mouse. That also makes a copy of the file within the folder.

While your solution works too, if you only need one of the worksheets, the original idea is still my preferred option, especially if you want to cut a worksheet to a new workbook.

An alternative to copying a worksheet for those who prefer to do everything by keyboard, is Ctrl+A, Ctrl+C, Ctrl+N, Ctrl+V.

I think all the possibilities are listed. But mostly it depends upon the user how it matters. Cool stuff

If you have a large worksheet with many rows and columns that mix sales areas, countries, states or sales people try this. Create a Pivot table from the data. Put the field you wish to separate the data by in the Report Filter area in Excel 2007, and select the value you want from the drop down. Add one field to the Value area (data). This should give you a single total, usually of the number of items. Click on this total and a new sheet will open containing only the rows for selection in the Filter area. Then use the Move or Copy option as above.

OLD STUFF. It would be easier to do save as.

I have found this a convenient but extremely dangerous practice. Because the Create a Copy checkbox is unchecked by default (a terrible idea, in my opinion) - if you forget to check it (and that's very easy to do - trust me) - you may think you have made a copy of your worksheet, rather than merely moved it. Your original worksheet is now GONE - if you edit or delete the moved one thinking you still have the original, you will be very disappointed. This has happened to me a couple of times over the years, and it is not pleasant!

As one who has suffered the same fate, I ask all to take note of the warning. As an aside, is there a way to check "Create a Copy" checkbox as default? An embedded macro, perhaps?

I would like to know if anyone can successfully get a macro to work for this. I tried recording a macro of the copy workbook actions (using copy to new workbook), but got errors when I tried to use it.

This works for me (in Excel 2003):

Sub MoveSheetsToDestinationFile()
'move sheets to Destination.xls..
sourcebook = ActiveWorkbook.Name
Workbooks.Open "DestinationFile.xls"
Windows(sourcebook).Activate
For Each wrkSheet In ActiveWorkbook.Sheets
wrkSheet.Move After:=Workbooks("Destination.xls").Sheets(1)
Next wrkSheet
Application.DisplayAlerts = False
End Sub

I only know how to record macros using the GUI, can you send a link or steps on how to make one using this text? Thanks in advance.

don't know why but it works for me
excell 2007

Sub Macro1()
Sheets("Sheet1").Select
Sheets("Sheet1").Copy
End Sub

I use this line:

ActiveSheet.Copy After:=Sheets(1)

within macros to copy the sheet I'm working on to manipulate data without losing the original workheet. This line creates a copy within the workbook in a specific place, but can be easily modified to put it in a new workbook with a "Workbooks.add" statement.

The recorded macro will....
Copy the same sheet to the same destination each time.
If the sheet already existed (from recording the macro in the first place) you would get errors because the sheet exists.

You need to write/amend the Macro (in VBA editor) if you want it to do something a little more 'sophisticated'.
Many online resources explain how to do this.

I use the originally suggested technique all of the time, but have "Always create backup" on all of the time to avoid the problem you suggest. I do a CTRL-S just before starting to manipulate the sheet.
The procedure also works well when you are trying to insert only one sheet of a workbook into the middle of another workbook with multiple sheets.

It is C&P just at the worksheet level.

Another quick way to move or copy a worksheet from one workbook to another location in the same workbook or to another existing workbook is to simply click and drag on the tab of an open worksheet to the destination. If you hold down the Ctrl key when you click and drag, it makes a copy. You can even drag into a different window. It helps to arrange the open windows horizontally or vertically to see where you're going.

I had to use a single Excel window, but it did work. Thanks.

This comment must come from me being jaded by years of sales an marketing management.

Ok, let???s say that this data is invaluable to sales and marketing. Let???s say that it gets worked on by 4 people with different responsibilities. Let???s also say that they move through the data in a rational manner, noting what they have done and what needs to be done next. Lets also say that they do this daily.

At the end of the month depending on their methodology you will have a complete record of all sales and marketing work done on these accounts, including vital comments and next actions, spread over between 4 and up to 100 spreadsheets.

Who is going to glue them all back together so that someone has an overview of what???s been done and what needs to be done? I wouldn???t like to be tasked with inventing a sales forecast from my data when it???s spread out to the 4 winds and inconsistently updated and merged back together.

But that???s me being silly and living too much in the commercial bits of a business!

Best wishes

Steve

The title of this article needs to be " Quickly move data to another workbook using Move and Copy "
Obviously it was targeted to beginner level users, and more information about the pitfalls (and Horrors) should not be left to only to the members of the discussion.

The only problem I have with the copy paste method is often my column width and row height won't transfer. By copying the tab I now have a mirror image of my original worksheet. I can now insert it between worksheets in another workbook. RGC

Thanks for the tip. I have been struggling with this forever. Does this also pull the page setup for print for the worksheet being copied?

iam using 2007 and not geting this option
please tell me how to enable this option

And the other issue with this technique is that formulas in the new workbook that reference worksheets in the original workbook still point to data in another workbook file. This can lead to consternation by users. You can avoid that be selecting the entire worksheet and Paste Special, Values. This limits some of the functionality in the new sheet that may or may not be required in a particular application.