Auto Copy using macro

December 6, 2010 at 02:05:16
Specs: Windows XP
Hi,

is there a way in macro wherein if a certain cell has value, the other cells will be automatically populated?

Example:

In cell E6 I put the date, then the values in cell A4, B4, C4 and D4 will be automatically copied to A6, B6,C6 and D6. Same goes when I enter the date on cell E7, the values in cell A4, B4, C4 and D4 will be automatically copied to A7, B7,C7 and D7 so on and so forth...

Thank you so much in advance.


See More: Auto Copy using macro

Report •

#1
December 6, 2010 at 05:42:16
You can do it with IF functions:

In A6 enter this formula:

=IF($E6<>"", A4, "")

Drag it across to D6.

Then edit each cell, highlighting the A4, B4, etc and press the F4 function key to get:

=IF($E6<>"", $A$4, ""), =IF($E6<>"", $B$4, ""), etc.

Now select A6:D6 and drag the formulas down as far as you need them.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 6, 2010 at 06:30:36
Is it possible for macro? I actually know how to use the formula but what happened was it made the size of my file big. (54,000 kb) in return I cannot open a new file because I don't have enough space. I'm thinking if it can be done in macro. :-)

Report •

#3
December 6, 2010 at 06:48:48
I have deleted the question that you posted in the other thread. As per the rules of this forum, when you submit duplicate posts, you run the risk of having both posts deleted.

Thanks!

DerbyDad03
Office Forum Moderator

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions

#4
December 6, 2010 at 06:56:43
It can be done with a macro, but we would need some more information:

Do you want the data copied as values, or do you want formulas placed in the cells so they they will always reflect the contents of A4, B4, C4 and D4 , even if they change?

Do you want the action to occur with any entry into Column E or only when a date is entered?

Do you want it to happen automatically as soon as something is entered in Column E or do you want to click a button so that you can decide when/if the copy should occur?

Since macros are very specific in what they do, the more details you provide regarding exactly what you want to happen - and when - will help us get it right.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#5
December 6, 2010 at 07:07:12
Here are the answer to your question. :-)

1. Only the values of A4, B4, C4 and D4.
2. only when a date is entered
3. happen automatically as soon as something is entered in Column.

Thank you so much in advance!


Report •

#6
December 6, 2010 at 07:41:52
Unclear Answers:

2. only when a date is entered
3. happen automatically as soon as something is entered in Column.

Does that mean that you will only ever be entering dates in Column E?

Should #3 read as follows?

3. happen automatically as soon as the date is entered in Column E

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#7
December 6, 2010 at 08:02:12
2. Only when a date is entered. The macro should identify if the data entered is in date format. Sometimes, users will accidentally entered a Text instead of numbers. I tried entering the value "a" in Column E and it was accepted. That is why I said that "Only when a date is entered.

3. Yes, happen automatically as soon as is entered in Column E


Report •

#8
December 6, 2010 at 08:03:01
3. Yes, happen automatically as soon as the Date is entered in Column E


Report •

#9
December 6, 2010 at 08:40:04
Those darn users!

Right click the sheet tab for the sheet where you want this to happen.

Choose View Code and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 5 Then
    If Not IsDate(Target) Then
      Application.EnableEvents = False
       MsgBox "You must enter a date in this cell"
       Target.ClearContents
      Application.EnableEvents = True
    Else:
       Range("A4:D4").Copy Destination:= _
         Range("A" & Target.Row & ":D" & Target.Row)
    End If
 End If
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#10
December 6, 2010 at 09:09:34
Hi,

This code works, but is it also possible that if Column E6 is blank, then A6, B6,C6 and D6 will be blank as well.

Also, is it possible that ONLY the value of A4, B4,C4 and D4 wil be copied, not the formats of the cells?

Thank you so much for all your help!


Report •

#11
December 6, 2010 at 09:21:23
re: "Is it also possible to do this and is it also possible to do that..."

First, I'll repeat what I said earlier:

Since macros are very specific in what they do, the more details you provide regarding exactly what you want to happen - and when - will help us get it right.

Second, since you didn't follow that suggestion, I'm going to go off and do other stuff while I wait for you to come up with more "also possible" things that you want the code to do.

It's pretty frustrating when we try to meet the requirements set forth in a request by writing and testing code - which entails setting up spreadsheets with data, entering the code, testing/fixing it, making it pretty and then posting it, only to see a response that says "Thanks. Now can you make it to this and that" - especially when I specifically asked that you tell us exactly what you want the code to do.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#12
December 7, 2010 at 02:30:43
Thanks a lot!

Report •

Ask Question