Copying entered data into unused cells?

BIGEYE

New Member
Joined
Feb 20, 2008
Messages
32
Can this be done, and if so....how?

Worksheet 1 is for data entry and will have the following row names
A1 = Date
A2 = Reading 1
A3 = Reading 2

The info will be entered periodically on Sheet 1. So I would enter the data alongside the names. Cell B1 I will enter the date. Cell B2 Reading 1 and Cell B3 Reading 2. The entered data is then copied into Worksheet 2 where the historical info is stored. So my column names will be
A1 = Date, B1 = Reading 1, C1 = Reading 2.

So the first time I enter my data into Worksheet 1, whatever date I enter into B1 would be copied into blank cell A2. Whatever data I enter into Worksheet 1 cell B2 would be copied into blank cell B2. And whatever data I enter into Worksheet 1 cell B3 would be copied into blank cell C2.

Next time around, I enter my data into Worksheet 1 to the same cells B1, B2 and B3. Whatever date I enter into B1 would be copied into the next blank cell A3. Whatever data I enter into Worksheet 1 cell B2 would be copied into the next blank cell B3. And whatever data I enter into Worksheet 1 cell B3 would be copied into the next blank cell C3. The original data entered would remain unchanged.

The data entry will be repeated, with the new data being copied to blank cells and the old data remaining unchanged.

Or is there another way?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I can write you a script do this but I would prefer to have this action happen when you double click on cell "A1" of Sheet (1)
Enter the data into your cells on sheet (1)
Then when your ready double click cell "A1" and all your data would be copied over to sheet (2)
The data you previously entered into Sheet(1) would be cleared an ready for you to enter more data.
This is my opinion is a better way then to expect things to happen automatically which sometimes could cause you problems. Double clicking cell "A1" would be very easy for you.
Would doing it this way work for you?
 
Upvote 0
I have decided to write the script the way I described in my earlier post
When you double click on cell "A1" in Sheet (1) all your data will be copied over to Sheet(2) as you requested.
On Sheet(2) you will need to have already entered headers.
Be sure and understand that Sheet(1) is always the sheet in the far left position on your sheet tab bar and sheet (2) would be next sheet to the right.

To install this code:

Right-click on the sheet (1) tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Cancel = True
Application.ScreenUpdating = False
Dim Lastrowa As Long
Dim Lastrowb As Long
Dim Lastrowc As Long
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowb = Sheets(2).Cells(Rows.Count, "B").End(xlUp).Row + 1
Lastrowc = Sheets(2).Cells(Rows.Count, "C").End(xlUp).Row + 1
Cells(1, 2).Copy Destination:=Sheets(2).Cells(Lastrowa, 1)
Cells(2, 2).Copy Destination:=Sheets(2).Cells(Lastrowb, 2)
Cells(3, 2).Copy Destination:=Sheets(2).Cells(Lastrowc, 3)
Sheets(1).Range("B1:B3").ClearContents
Sheets(1).Range("B1").Select
Application.ScreenUpdating = True
End If
End Sub
 
Last edited:
Upvote 0
I have decided to write the script the way I described in my earlier post
When you double click on cell "A1" in Sheet (1) all your data will be copied over to Sheet(2) as you requested.
On Sheet(2) you will need to have already entered headers.
Be sure and understand that Sheet(1) is always the sheet in the far left position on your sheet tab bar and sheet (2) would be next sheet to the right.

To install this code:

Right-click on the sheet (1) tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Cancel = True
Application.ScreenUpdating = False
Dim Lastrowa As Long
Dim Lastrowb As Long
Dim Lastrowc As Long
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowb = Sheets(2).Cells(Rows.Count, "B").End(xlUp).Row + 1
Lastrowc = Sheets(2).Cells(Rows.Count, "C").End(xlUp).Row + 1
Cells(1, 2).Copy Destination:=Sheets(2).Cells(Lastrowa, 1)
Cells(2, 2).Copy Destination:=Sheets(2).Cells(Lastrowb, 2)
Cells(3, 2).Copy Destination:=Sheets(2).Cells(Lastrowc, 3)
Sheets(1).Range("B1:B3").ClearContents
Sheets(1).Range("B1").Select
Application.ScreenUpdating = True
End If
End Sub

Thanks, works a treat.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top