Fill column in sheet1 with data entered in one cell from sheet2 ..

B

barneyboy

Board Regular
Joined
Feb 28, 2009
Messages
71
Hi .. I would like to fill a column in sheet1 from data that I enter into only one cell in sheet2, eg:

sheet2 A1 I enter 5 and it appears in A1 of sheet1.

I now go back to sheet2 A1 and enter fresh data, say 20 and that 20 now appears in cell A2 of sheet1.

I now go back to sheet2 and enter fresh data into A1 and that data appears in cell A3 of sheet1, and so on.

I'm only using cell A1 of sheet2 to enter data .. does this make sense ?

Any help will be much appeciated ..:p
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Put this in the Sheet2 module:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, [A1])
Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)(2) = rng
End Sub
 
Upvote 0
Try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet (2).Tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window




Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
If Sheets(1).Range("A1").Value = "" Then
Lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
Else
Lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1
End If
Target.Copy Destination:=Sheets(1).Range("A" & Lastrow)
End If
End Sub
 
Upvote 0
Hi .. thanks for responding .. I'll give it a try.

footoo said:
Put this in the Sheet2 module:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, [A1])
Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp)(2) = rng
End Sub
Click to expand...
 
Upvote 0
Hi .. This works exactly as I wanted it to .. the only thing is I wanted to sum all the data that I entered but I didn't say that in my original post .. my fault .. is there any way you could add this to the above code?

Many thanks in advance and it's greatly appreciated,

bb

My Aswer Is This said:
Try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet (2).Tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window




Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
If Sheets(1).Range("A1").Value = "" Then
Lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
Else
Lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1
End If
Target.Copy Destination:=Sheets(1).Range("A" & Lastrow)
End If
End Sub
Click to expand...
 
Upvote 0
You want to sum all the values in Column "A" of sheet (1)?
And where do you want the results of this summing entered?
 
Upvote 0
Yes, that's correct .. I want to sum the values in column A of sheet1 and have the result placed in A20 of column A in sheet1 and then I want to be able to clear A20 after I've utilized the data then begin the process all over again as I require. Apologies for not being clear.

Many thanks,

bb
 
Upvote 0
You said:
I want to be able to clear A20 after I've utilized the data
How are we to know when you have "utilized" the data?

And your wanting to keep entering values into column "A" one after another but then want the sum also put into column "A" this makes things more complicated and I'm not sure how to do that. Now if you want the sum put in say "B1" that would be simple. And how to know when to clear the sum is another question.
 
Upvote 0
Ok .. putting the sum in B1 would work .."And how to know when to clear the sum is another question." .. I determine when to clear the sum e.g.:

A B
5
5
5
15

I now have what I want .. there is no further need for that data anymore as I have used the 15 somewhere else but I need to clear column A for another set of data to be summed in exactly the same way but there may be more than three entries in column A this time; and so on ..

I greatly appreciate your help .. we're almost there ..

bb
 
Upvote 0
Try this:

This script will put the sum of all the values in Range("B1") of sheet (1)
When you want to clear all the data:
Double click on Range("A1") of sheet (2)

Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Cancel = True
Sheets(1).Range("A:A").ClearContents
Sheets(1).Range("B1").ClearContents
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Dim ans As Long
If Sheets(1).Range("A1").Value = "" Then
Lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1
Else
Lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1
End If
Target.Copy Destination:=Sheets(1).Range("A" & Lastrow)
End If
ans = Application.WorksheetFunction.Sum(Sheets(1).Range("A1:A" & Lastrow))
Sheets(1).Range("B1").Value = ans
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
VBA copy paste cells one by one
Replies
1
Views
44
Joe4
Joe4
R
  • Question
VBA Check if value in Sheet1 exists in Sheet2 then copy specific cells from Sheet2 into Sheet1
2
Replies
13
Views
738
dermie_72
D
C
  • Solved
Column data selection drop-down
Replies
5
Views
309
SanjayGMusafir
SanjayGMusafir
N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
187
DanteAmor
DanteAmor
D
  • Question
Pull Row Of Values from Sheet2 Into Sheet1
Replies
1
Views
346
data808
D

Forum statistics

Threads
1,214,990
Messages
6,122,625
Members
449,093
Latest member
catterz66

Share this page

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