copy data from sheet1 to sheet2

January 11, 2010 at 23:45:17
Specs: Windows XP
need to create a macro for excel that copies data in specific cells in Sheet1 to the next empty row in Sheet2. ...

See More: copy data from sheet1 to sheet2

Report •

January 12, 2010 at 03:22:21

I'd like to offer a couple of posting tips.

1 - When requesting help in a forum, it's customary to actually ask a question instead of simply stating a need. We're all volunteers here, offering help for free, so we like to keep it friendly.

2 - It also helps if you provide enough detail so that we can help in the most efficient manner. We have no way of knowing what you mean by "specific cells" nor do we know when you want this copy to occur e.g. on-demand or when something changes in the sheet, etc.

Please give us a little more detail - and ask nicely - so that we can help you accomplish your goal.

Report •

January 15, 2010 at 23:13:39
I do apologise for my "blunt" message. but I meant no dis respect to anyone, I was trying to get to the point as much as possible but it seems I failed. Again I apologise and appreciate any assistance you can give me.


This is my scenario, I am working on three sheets in my workbook; "Requisition", "Vendors" & "Records"

The Requisition sheet works as a form I use for entering line item data that extends to several cells and columns in the same row.

After all data is input in the "requisition" sheet, I would like to run a macro that will copy all the data in the range of cells and columns from the "Requisition" sheet to the next available row in the "Records" sheet

Report •

January 16, 2010 at 06:02:50

You haven't said how your requisition data is arranged on the Requisition sheet, so I have assumed that it is in row 2, allowing for headings in row 1.
I have assumed that the requisition consists of 5 cells A2 to E2.
You can change the range easily by adjusting the code. However if your source requisition information is not in a single block of cells, the code will require more 'structural' changes.

On the destination 'Records' sheet I have put a sequential record number in column A - the code increments this automatically for each new record.
The requisition data then goes into the next empty row in columns B to F.
The columns are re-sized to accommodate the new record.

The source data on the requisition page is erased.

If you use sequential order numbers you could get this macro to pre-fill the requisition with the next order number.

You could add a test for missing data in the requisition and pop up a message box, and not copy the data - Just enclose the core of the code in an If statement, testing each of the requistion cells.

You could also add data checks - for instance to check that a an ordered item was in your inventory, and you could add to the record by looking up items in the order, possibly adding a phone number based on the name - using a table of known customers.

I suggest that you add a command button on the requisition sheet to run the macro.

Worksheet command button:
On the sheet with the source data create a command button from the Control Toolbox toolbar.
(If this toolbar isn't visible, right click on an existing toolbar and check the Control Toolbox from the list that appears).
Select the button Icon and draw a button.
Right-click the button and select Command Button - Edit and change the name to something useful!
Right-click the button again and select View Code.
In the visual basic code window that opens enter the code.
Note that Private Sub CommandButton1_Click() and End Sub will already be there, so don't duplicate them.
Option Explicit goes before the line Private Sub CommandButton1_Click().

Option Explicit

Private Sub CommandButton1_Click()
Dim rngDestRow As Range

On Error GoTo ErrHnd

With ActiveWorkbook
    'get row below last used row on Records sheet
    'records start in column B (col A is sequential record number)
    Set rngDestRow = .Worksheets("Records").Range("B65534").End(xlUp).Offset(1, 0)
    'create a record number in column A - increment number in row above, column A
    rngDestRow.Offset(0, -1).Value = rngDestRow.Offset(-1, -1).Value + 1
    'copy source on Requisition page - assume source in cells A2 to E2
    .Worksheets("Requisition").Range("A2:E2").Copy _
    'resize columns (resize range to include row 1 headings down to this entry
    rngDestRow.Offset(-rngDestRow.Row + 1, 0).Resize(rngDestRow.Row, 5).Columns.AutoFit
    'clear source cells
End With
Exit Sub

'error handler
End Sub

After entering the code click 'Save' from the Visual Basic Menu.
Use Alt+f11 (the Alt key and function key #11 together), to swap back to the main Excel window.
Exit Design mode - click the first icon on the Control Toolbox toolbar.
Click the Command button to run the code.

As this code finds the next row to use on the Records worksheet by using the End(xlUp) action, there must not be any data below the last record, at least in column B that this code uses for the search.

You mention columns as well as requisition data in a row of cells. Include this line of code to copy a second range of cells in the requisition worksheet and copy them to the same row on the records worksheet, but 5 columns further over - hence the Offset. You will also need to add a second Clear statement to empty these cells, as well as expanding the number of columns in the Autofit line.

    .Worksheets("Requisition").Range("A4:E4").Copy _
        Destination:=rngDestRow.Offset(0, 5)

Regarding data integrity you could add data validation to the requisition cells, such as only allowing today's date, or only allowing numbers for an order quantity.

Hope this gives you something to work from.


Report •

Related Solutions

January 17, 2010 at 04:40:30
Thanks Humar,

the code works to some extent and appreciate your help.

Firstly my "requisition" data is in cell range A2:E48. The code copies the data includeing border formats and cells which do not have any data. Is it possible for it to copy only the cells which have data


Report •

January 17, 2010 at 06:17:05

You say that the code works to some extent - to be pedantic I think that the code works fully - it does what I said it would do. You asked for code that will copy all the data in the range of cells and columns.

You didn't say what the range was and you didn't mention that some cells were empty and you didn't want them copied!!!

You still haven't said how the data from cells on the requisition worksheet are to be copied to the records worksheet. Are all cells with data copied sequentially to the destination row. If sometimes a source cell is empty and sometimes it contains data, the data copied to the records worksheet will be in different columns, resulting in inconsistent data in some of the columns. Is this acceptable?

You don't say if you want the source cells emptied after they have been copied. I have assumed you do, so the following code clears cells that had data. You also don't mention if the data in any of the source range of cells is created by a formula - as a result before clearing a cell I have added a test for a formula and don't clear the cell if it contains a formula.

To copy a range of data but to exclude empty cells requires a different approach to the copy and paste process than copying a complete range. It is a cell by cell copy and paste. This is much slower and also results in a lot of screen flicker. As a result I have turned off screen updating whilst the code is running.

The following code copies all cells with data in the range A2 to E48.
The data is placed sequentially into cells in the next empty row of the records sheet, starting at column A.
(All cells below the last data row in column A must be empty for this to work properly).
When copying data, only the value and any number formatting is copied. Cell borders, backgrounds, font attributes are not copied.
Any cell that contains data in the source range and is not a formula is set to empty, i.e., "". The Cells are not 'cleared', so that any cell formatting is preserved.

Option Explicit

Private Sub CommandButton1_Click()
Dim rngCell As Range
Dim rngDestRow As Range
Dim intDestCol As Integer

On Error GoTo ErrHnd

'stop screen updating to stop flicker
Application.ScreenUpdating = False

With ActiveWorkbook
    'get row below last used row on Records sheet
    'records start in column A
    Set rngDestRow = .Worksheets("Records").Range("A65534").End(xlUp).Offset(1, 0)
    'set destination column counter
    intDestCol = 0
    'copy source on Requisition page - only cells with data
    For Each rngCell In .Worksheets("Requisition").Range("A2:E48")
        'copy cell only if not empty
        If rngCell.Value <> "" Then
            'paste the values and number format but not cell formats/borders etc.
            rngDestRow.Offset(0, intDestCol).PasteSpecial _
            'next column
            intDestCol = intDestCol + 1
            'set source cell to empty unless it contains a formula
            If Not rngCell.HasFormula = True Then
                rngCell.Value = ""
            End If
        End If
    Next rngCell
End With
'restore screen updating
Application.ScreenUpdating = True
Exit Sub

'error handler
Application.ScreenUpdating = True
End Sub

I have tested this on some dummy data I created, including cells with formatting - borders, background and font formats, as well as cells with formulas.
Obviously I cannot tell if it will work on your data. Please ensure that you test it on copies of your data.
As changes made by Macros cannot be undone with the Undo function, always make a copy of the workbook before running this Macro.


Report •

January 18, 2010 at 18:27:16

The code works perfectly. I am extremely grateful for your help. Thanks


Report •

Ask Question