Auto copy and paste

If Cell "H1" is a header then you should be able to use the following code. I inserted this code into the worksheet "Sheet2". Hope that works out for you.

Private Sub Worksheet_Change(ByVal Target As Range) 'If there are any changes in THIS worksheet the macro will run
Application.ScreenUpdating = False 'Disables screen updating
ActiveWorkbook.Sheets("Sheet2").Activate ' Activates Sheet2
Columns("H:H").AutoFilter Field:=1, Criteria1:="<>" 'Filters Column H to Non-Blanks
Sheets("Sheet1").Range("D:D").ClearContents 'Clears Column D in Sheet1
Range("H:H").Copy 'Copies Column H non-blank cells
Sheets("Sheet1").Range("D1").PasteSpecial 'Pastes non-blanks into Sheet1 Column D starting in Cell D1
Sheets("Sheet2").Columns("H:H").AutoFilter 'Clears the AutoFilter
Application.ScreenUpdating = True 'Enables screen updating
End Sub

Originally Posted by Tom

Hi all,
Is there a way to copy only all NON BLANK cells in column H on Sheet2 over
to column D of Sheet1 and have this run without having to press a button to
start the macro...have it "live" update?

Thanks!

Thanks for the reply Ikaabod,
I tried using this macro, but it doesn't copy the text over to sheet1. Just
thinking here...does it matter if I have a formula in column H on
sheet2...could that be interfering with the way this macro works? It copies
the formula over to Sheet1...but not the actual cell contents.

Thanks!
Tom

"Ikaabod" wrote:

>
> If Cell "H1" is a header then you should be able to use the following
> code. I inserted this code into the worksheet "Sheet2". Hope that
> works out for you.
>
> Private Sub Worksheet_Change(ByVal Target As Range) 'If there are any
> changes in THIS worksheet the macro will run
> Application.ScreenUpdating = False 'Disables screen updating
> ActiveWorkbook.Sheets("Sheet2").Activate ' Activates Sheet2
> Columns("H:H").AutoFilter Field:=1, Criteria1:="<>" 'Filters Column H
> to Non-Blanks
> Sheets("Sheet1").Range("D:D").ClearContents 'Clears Column D in Sheet1
> Range("H:H").Copy 'Copies Column H non-blank cells
> Sheets("Sheet1").Range("D1").PasteSpecial 'Pastes non-blanks into
> Sheet1 Column D starting in Cell D1
> Sheets("Sheet2").Columns("H:H").AutoFilter 'Clears the AutoFilter
> Application.ScreenUpdating = True 'Enables screen updating
> End Sub
>
> Tom Wrote:
> > Hi all,
> > Is there a way to copy only all NON BLANK cells in column H on Sheet2
> > over
> > to column D of Sheet1 and have this run without having to press a
> > button to
> > start the macro...have it "live" update?
> >
> > Thanks!
>
>
> --
> Ikaabod
> ------------------------------------------------------------------------
> Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
> View this thread: http://www.excelforum.com/showthread...hreadid=542197
>
>

I just played around with the code a bit and got it to work!!! Your 'comments
after each line of code sure do help VB dummies like Yours Truly understand
what is going on and able to learn as we go along.
Thanks for the help!
Tom

"Tom" wrote:

> Thanks for the reply Ikaabod,
> I tried using this macro, but it doesn't copy the text over to sheet1. Just
> thinking here...does it matter if I have a formula in column H on
> sheet2...could that be interfering with the way this macro works? It copies
> the formula over to Sheet1...but not the actual cell contents.
>
> Thanks!
> Tom
>
> "Ikaabod" wrote:
>
> >
> > If Cell "H1" is a header then you should be able to use the following
> > code. I inserted this code into the worksheet "Sheet2". Hope that
> > works out for you.
> >
> > Private Sub Worksheet_Change(ByVal Target As Range) 'If there are any
> > changes in THIS worksheet the macro will run
> > Application.ScreenUpdating = False 'Disables screen updating
> > ActiveWorkbook.Sheets("Sheet2").Activate ' Activates Sheet2
> > Columns("H:H").AutoFilter Field:=1, Criteria1:="<>" 'Filters Column H
> > to Non-Blanks
> > Sheets("Sheet1").Range("D:D").ClearContents 'Clears Column D in Sheet1
> > Range("H:H").Copy 'Copies Column H non-blank cells
> > Sheets("Sheet1").Range("D1").PasteSpecial 'Pastes non-blanks into
> > Sheet1 Column D starting in Cell D1
> > Sheets("Sheet2").Columns("H:H").AutoFilter 'Clears the AutoFilter
> > Application.ScreenUpdating = True 'Enables screen updating
> > End Sub
> >
> > Tom Wrote:
> > > Hi all,
> > > Is there a way to copy only all NON BLANK cells in column H on Sheet2
> > > over
> > > to column D of Sheet1 and have this run without having to press a
> > > button to
> > > start the macro...have it "live" update?
> > >
> > > Thanks!
> >
> >
> > --
> > Ikaabod
> > ------------------------------------------------------------------------
> > Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
> > View this thread: http://www.excelforum.com/showthread...hreadid=542197
> >
> >