Auto-Copying Data Entries from One Sheet to Another

[Awic H. Deles]

Hi there, hope someone here can help me or at least point me in the right
direction....

Here's my dilemma: I need to copy data entries in one column of Sheet 1
"automatically" to Sheet 2 (which means as I encode/type them in, they would
"magically" appear on a particular column in Sheet 2) Now, I have a fairly
good idea on how to do this (or at least I think I do ...by just putting a
"+' sign on each a particular column cell of Sheet 2 and moving back my
highlight to Sheet 1 column cell, I am able to auto-duplicate whatever data
entry is put in Sheet 1 cell....and so on...by just copying that "formula"
in Sheet2

However....here's the "challenge"....How can I make data entries in Sheet 2
"follow" Sheet1 entries wherein the entries will auto-appear on the first
blank cell of Sheet 2??? instead of copying whatever is the entry on a
particular cell..which will result in a blank spaces in the column. Am I
making any sense? ;-)

Here's an illustration:

Sheet 1 Sheet2

CellNo. 1DataEntry1 1DataEntry1
2DataEntry2 2DataEntry2
3 NoData Entered 3DataEntry3 <---this gets moved up
to Cell no. 3 because Cell no.3 has No Data Entered
4DataEntry3 4.DataEntry4<---same with this
one coz Cell no.5 does not have a Data Entered then, whatever is enterd in
CellNo.6 takes
its place...
5NoData Entered
6.DataEntry4

The above will then have a continuous column without any space in between on
Sheet2

Thanks!

Allen
Email: (e-mail address removed)

 

[Bernie Deitrick]

Allen,

Try this technique, though it may not be great for large datasets or if you
are constantly changing your data set size:

http://www.cpearson.com/excel/noblanks.htm


Or, use the Worksheet's change event: though if you have other data on
sheet2, this code as written will likely destroy the data structure. Copy
the code, right click on sheet1's tab, select "View Code" and paste the code
into the window that appears.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Worksheets("Sheet2").Range("A:A").ClearContents
Target.EntireColumn.AutoFilter Field:=1, Criteria1:="<>"
Target.EntireColumn.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("Sheet2").Range("A1")
Target.EntireColumn.AutoFilter
Application.EnableEvents = True
End If
End Sub

 

[Awic H. Deles]

Thanks!!! May need a little tweaking (as I want the "NoBlanks" data to
appear in a different worksheet) but I'm sure it'll do.