Auto copy and paste
 

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!

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

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

Auto copy and paste
 

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

Auto copy and paste
 

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