Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF Criteria Auto-Update on Copy/Paste | Excel Discussion (Misc queries) | |||
auto copy and paste | Excel Discussion (Misc queries) | |||
Auto-Filter copy/paste problem | Excel Discussion (Misc queries) | |||
Auto "copy and paste" individual cells from various sheets into one sheet ?? | Excel Discussion (Misc queries) | |||
my auto cell hiliting procedure kills copy/paste | Excel Programming |