Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 14th, 2011, 02:01 AM
Registered User
 
Join Date: Feb 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need code for copy paste values after change event

Hello,
I am looking for simple code. I tried myself but can't seem to get it to work.

In the A column, user will be entering values. In the B column, I have a formula that will enter the system time ("now") only when a value is entered in A.

I need macro code so that once a value is entered in column A, the values from column B are pasted special (as values) into column C.

I think I need to use Worksheet_change and the following code line.

Code:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Also, there is no way to do this simply with a formula and without macros is there?
 
Old March 11th, 2011, 04:52 AM
Registered User
 
Join Date: Feb 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try this
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Target.Offset(0, 2) = Target.Offset(0, 1)
End Sub
 
Old March 11th, 2011, 06:11 AM
Registered User
 
Join Date: Mar 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Hello

Is the code working properly, Please Give Us an reply http://www.emphasizebpo.com
 
Old March 11th, 2011, 07:16 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

I'm guessing your column B has an If() function that displays Now() only if there's a value in column A. You can skip using that If() entirely and just enter the value in column C (or B for that matter). The following code puts the current date/time in column b if something was entered in column a, and also deletes the date/time in column b if one or more rows are deleted in column a.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 1 And Target.Column = 1 Then 'something has been done in column A
    If Target.Rows.Count = 1 Then  'a value has been entered/changed/deleted in one row of column A
        If Len(Target) > 0 Then 'something has been entered or changed in column A
            Target.Offset(0, 1) = Now
        Else 'a value has been deleted from one row, delete DateTime for that row
            Target.Offset(0, 1).Clear
        End If
    Else 'a value has been deleted from multiple rows, so delete the DateTimes for those rows
        Target.Offset(0, 1).Clear
    End If
End If

End Sub





Similar Threads
Thread Thread Starter Forum Replies Last Post
Speed up code - looping and copy / paste vba_user Excel VBA 6 March 23rd, 2011 05:27 PM
Need Java code for "print screen copy and paste" should act as an attachement on jsp. Dev programmer Java Basics 0 December 13th, 2010 06:26 AM
Copy and Paste the value waka Excel VBA 7 October 28th, 2009 10:08 PM
copy paste !!!!!!! dpkbahuguna Beginning VB 6 1 March 30th, 2007 10:06 AM
Copy, Paste dpkbahuguna Beginning VB 6 1 October 26th, 2006 10:30 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.