Excel: How do you export data from one excel sheet to another?

Question

Related Questions:

How do you avoid named cell conflicts (e.g. if they both have cells called "cellName")?

Is there a single easy command to take a single block of data and make it into a new sheet with a certain name.

How do you rearrange sheets?

Sorry, I forgot to add, WITH A MACRO (just capitalized for emphasis, I'm not mad or anything)

Answer 1

You are kind of asking a whole bunch of different questions all in one so it makes this kind of hard to answer.

As for as avoiding the named cell conflicts, that is probably due to the fact that named ranges normally have a workbook wide level scope. That means you could pick a group of cells that consists of a few cells from each and every worksheet of your workbook and then go to the Name box and name the range. And then the name for that range would include all of those cells. So normally that name can only be used once inside of the workbook.

If however you want to refer to Cell C3 as InterestRate on all of your worksheets and you want to refer to just one cell on each worksheet and not a group of all the C3 cells you have to reduce the scope down to the worksheet level. To do that, you have to add the worksheet name followed by an exclamation point then the name. So if I was on Sheet2 and Range C3 on Sheet2 was named interest rate and I wanted to refer to C3 on Sheet1 that I also named interest rate, then I'd refer to that named range as:

Sheet1!InterestRate

If you are referring to that range on the same page you don't have to include the worksheet name followed by a parenthesis. So for the named range InterestRate on Sheet2 you'd only have to specify InterestRate as long as the active sheet is Sheet2.

As far as moving the worksheets around, all you have to do is use the Move method. The move command is followed by a before or after designation.

This example moves Sheet1 right after Sheet3.

Worksheets("Sheet1").Move after:=Worksheets("Sheet3")

This example moves Sheet1 right before Sheet3.

Worksheets("Sheet1").Move before:=Worksheets("Sheet3")

In additions to referencing the worksheets by their name you can also reference them by the number of their position. The one at the far left is one, the one to the immediate right is 2, and so on.

So if Sheet1 is the first sheet and Sheet3 is the third sheet, you could restate the first example like this:

Worksheets(1).Move before:=Worksheets(3)

The number refers to the placement of the worksheet from left to right.

There are tons of other ways of doing copies also.

You could name the range then copy and paste the range. Or if it is a block, you can use:

ActiveCell.CurrentRegion

This will help you out. You can use the Address property of that to get the name of the range.

ActiveCell.CurrentRegion.Address

What that does is get the address of the block of cells with data in them that surround the active cell. You can pass this off as a Range by passing the address to a variable that is set as a string. For example:

Dim RangeAddress As String

RangeAddress = ActiveCell.CurrentRegion.Address

Range(RangeAddress).Copy
Worksheets.Add
ActiveWorksheet.Range("A1").Paste

This coding will copy the block of cells surrounding your active cell (where you have your cursor) and will paste it into a new worksheet starting in Cell A1.


If you are having trouble, another thing you can do is use the Macro Recorder then go back and look at the coding that it created. Go to Tools > Macro > Record Macro. Then go back and instead of running the macro, click the edit button and that will allow you to see the coding.

Answer 2

There are many ways to do this. The easiest is to just copy the data to the second sheet. Block the column or row you want and open the second sheet and past it where you want.

You avoid name conflicts if the name really means something in relation to the data.

Yes with the Block and copy method. Open the new sheet first and give it a name.

You can block copy and paste rows or columns as you wish.

Answer 3

1. In the cell you want to export to place =, go to the cell you want to copy and left click, hit return.
2. Hold left button down in top corner of group of cells you want to copy. Move across and down so all cells are highlighted. Release button. Right click for menu. Click on copy. Go to new sheet and position at top corner of cells you want to copy to. Right click for menu and click on paste. Hie Esc to complete. Note, if the cells you're copying are filled by formulas, then this won't work.
3. Left click on tab of sheet bto be moved at bottom of workbook. Drag to new position.