Moving Data to a new Worksheet

[Barrywillb]

With the use of Excel VBA and/or macro’s, how do you take data from a selected cell or row on one worksheet and move that data or row data to specified locations on another worksheet in the same workbook?

[royUK]

Macros & VBA re not different, macros are created using VBA.

This question is cvered extensively ib many questions on the Forum. Take a look at the Contacts form herehttp://www.excel-it.com/vba_examples.htm

[MatrixMan]

As Roy says, you should check the examples as this will give you a good feel for the referencing conventions; however, I thought a quick summary might also help and give context to the examples. As you probably know, the basic structure for a copy/paste is:

Range("A1").Copy Destination:=Range("B2")

If you ran the above code from the workbook object, it would copy cell A1 to cell B2 in the active sheet. If you ran it within a worksheet object, it would copy and paste the same cells in that worksheet, regardless of which sheet was active at the time.

In order to explicitly specify the from sheet / to sheet you just need to specify the worksheet/s in the statement such as:

Worksheets(1).Range("A1").Copy Destination:=Worksheets(2).Range("B2")

In this case, the worksheets are explicit, so it doesn't matter which is active or which object you're running the code from. This can be a little confusing if you're not familiar with the object heirarchy, but basically: if you do not specify a sheet, excel assumes you mean the active sheet if the code is running in the workbook object, or - if the code is running within a worksheet rather than in the workbook object - then excel assumes you mean that worksheet (rather than the activesheet). Of course, if you use only an explicit worksheet reference for (say) the paste destination, then the same heirarchy rules apply for the copy range. Hope that helps. MM.

[royUK]

Or you don't need to copy

Worksheets(2).Range("B2")=Worksheets(1).Range("A1")