Automatically transferring data from one row to another

**src16** · 01-21-2013, 09:02 PM

Hi,

I have this worksheet with over 3000 rows and 25 columns of data. The data starts on Row 3. It is essentially a tracker spreadsheet used by many departments in my company. The data in the 3000 rows & 25 columns is not disclosed to all departments, and is hidden and locked. What I require is to set up the worksheet so that if a user types in information in row 2 (row 1 will have the column headings), the data gets cut and paste (or transferred) to the last row of the worksheet when the user presses "Enter" or a button. Essentially, it gets added to the list. I am unfamiliar with macros and VBA but I am eager to learn. Any help is appreciated.

**arlu1201** · 01-22-2013, 05:10 AM

To which worksheet should the row be copied?

**src16** · 01-22-2013, 12:29 PM

The row should be transferred (cut and paste) to the same worksheet...but the last row...Basically, the information that is typed should be entered to the end of the list.

**arlu1201** · 01-22-2013, 01:02 PM

So row 2 should be deleted so other rows move up or should row 2 contents be cleared ?

**src16** · 01-22-2013, 02:39 PM

Row 2 contents should be cleared so that the user can type in another record should they wish. Thanks!

**thameem127** · 01-22-2013, 02:43 PM

Upload sample file.

**src16** · 01-22-2013, 07:09 PM

The sample file has been attached. Rows 3 to 27 have been hidden. So basically when a user types in Row 2 and presses "Enter", that row should be cleared off and the data would go into Row 28. Thanks!

**arlu1201** · 01-23-2013, 05:30 AM

Will column R always be the last column? Is it ok if a button is provided so you can enter the data in the row and then press the data and it gets copied to the last row?

**src16** · 01-23-2013, 08:09 AM

Yes, column R will always be the last column and a button will work as well, although as I am unfamiliar with VBA/macros, it would be helpful if perhaps I was walked through how the whole thing works. Thanks!

**arlu1201** · 01-23-2013, 12:40 PM

Try this code -

Option Explicit

Sub update_data()
Dim lrow As Long

With Worksheets("Sheet1")
    lrow = .UsedRange.Rows.Count
    .Rows("2:2").Copy
    .Range("A" & lrow + 1).PasteSpecial (xlPasteValues)
    .Range("A2:R2").ClearContents
End With

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

Or you could put a button on your sheet and assign the macro to the button.

**src16** · 01-23-2013, 01:35 PM

Thanks! This works perfectly except the data does not paste in Row 28 (the first unused row). Instead, it pastes a few rows after Row 28. Any idea why that is?

**arlu1201** · 01-23-2013, 01:56 PM

I had to calculate the lastrow (lrow) using the usedrange property. So this way, if you put any formula in A28 for example and then clear it, it still thinks that cell is used. So thats how it goes one row below. Try deleting rows 28 to 30 maybe and see if the code pastes in 28.

If not, i will have to think of another solution.

**src16** · 01-23-2013, 02:24 PM

Yes that's exactly what I thought. I deleted the rows and now it works perfectly. However there is one column (Column S), which has a formula. I didn't think to mention it as the user does not have any access to it. But is there any way for the formula to fill the next row as soon the user clicks the button and populates that row?

I had the blank rows autofilled with the formula, but as you correctly deduced: the data does not paste in that row. Thanks!

**arlu1201** · 01-23-2013, 03:06 PM

You mean if row 27 was the last populated row, then S28 should have the formula from S27?

**src16** · 01-23-2013, 03:30 PM

Yes exactly! Column S has nothing to do with any of the columns associated with user input. It is just a column for referencing purposes. It basically has the row number (i.e. ROW(S1)).

**arlu1201** · 01-23-2013, 03:34 PM

So either you can drag down the formula to maybe 100 rows at the outset (since its a small formula) or you could use this code -

Option Explicit

Sub update_data()
Dim lrow As Long

With Worksheets("Sheet1")
    lrow = .UsedRange.Rows.Count
    .Rows("2:2").Copy
    .Range("A" & lrow + 1).PasteSpecial (xlPasteValues)
    .Range("A2:R2").ClearContents
    .Range("S" & lrow).Formula = .Range("S" & lrow - 1).Formula
End With

End Sub

**src16** · 01-23-2013, 04:57 PM

Hey! Thanks! The only issue is that though it's copying the formula, it is unable to copy/fill down the cell reference. For example, it is copying the formula ROW(S27) into Row 28. So the value in S28 is shown as 27, when it should be 28. Other than that, everything else looks great!

**arlu1201** · 01-24-2013, 01:51 AM

Updated code -

Option Explicit

Sub update_data()
Dim lrow As Long

With Worksheets("Sheet1")
    lrow = .UsedRange.Rows.Count
    .Rows("2:2").Copy
    .Range("A" & lrow + 1).PasteSpecial (xlPasteValues)
    .Range("A2:R2").ClearContents
    .Range("S" & lrow + 1).FormulaR1C1 = .Range("S" & lrow).FormulaR1C1
End With

End Sub

**src16** · 01-24-2013, 09:23 AM

Thanks! That worked!

Automatically transferring data from one row to another

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Re: Automatically transferring data from one row to another

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions