Excel 2010, copy & paste cells into another sheet based on non-blank cell

I want to develop a formula that automates and updates this process on the fly: In Worksheet 1, if and when there is a value entered into cell V2, then the values in D2, L2, M2 and V2 are copied and pasted (paste special - values) into Worksheet 2 in the following order: V2 into A2, D2 into B2, L2 into C2, and M2 into D2. The workbook is shared. What is the most efficient way to do this?

Thanks,
Andrea
AndreamaryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FamousMortimerCommented:
Hi Andrea,

Please see the attached workbook and let me know if you have any questions.

Here is the code I used in the ThisWorkbook object

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("V2"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Sheet2.Range("A2").Value = Range("V2").Text
        
        Sheet2.Range("B2").Value = Range("D2").Text
        
        Sheet2.Range("C2").Value = Range("L2").Text
        
        Sheet2.Range("D2").Value = Range("M2").Text
        Application.ScreenUpdating = True
    End If
End Sub

Open in new window

test.xlsm
0
AndreamaryAuthor Commented:
Hi,
Thanks very much for the quick response! My apologies, but I'm new to inserting code - would you be able to outline how get this into my own spreadsheet. Worksheet 1 in my spreadsheet is called "IAP_Charts_Pub" and Worksheet 2 is called "Cartography". Thanks in advance for your patience.

Andrea
0
FamousMortimerCommented:
Sure no problem.

Hold ALT and hit F11.  This will bring up the VBA window.

On the left you should see the project explorer which shows each sheet and a ThisWorkbook object for every workbook you have open.  If not Hold CTRL and hit R.

Double-click Sheet# (IAP_Charts_Pub) under the workbook in question.  A code window will appear on the right.

Copy and paste my code below into the window and test it out.

Make sure that you save it as an *.xlsm file (xlsx will not save code) if using Excel 2007+

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("V2"), Target) Is Nothing Then
        Application.ScreenUpdating = False

        Sheets("Cartography").Range("A2").Value = Range("V2").Text
        Sheets("Cartography").Range("B2").Value = Range("D2").Text
        Sheets("Cartography").Range("C2").Value = Range("L2").Text        
        Sheets("Cartography").Range("D2").Value = Range("M2").Text

        Application.ScreenUpdating = True
    End If
End Sub

Open in new window

Let me know how it goes.

-FM
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

AndreamaryAuthor Commented:
Hi FM,

I haven't been able to replicate your success at my end. I went back to basics, started with a new file, and tried to recreate your test.xlsm file, but find that mine doesn't work. I am making sure that macros are enabled, etc. and have tried this on 2 different computers. I've attached my attempt, and wondered if you could have a look at it to see where I'm going wrong...

Thanks!
Andrea
RecreatedTest.xlsm
0
AndreamaryAuthor Commented:
Finally had success...I'm not sure what I was doing wrong. In the end, I copied the text in row 2 on the IAP_Charts_Pub sheet to row 3, deleted row 2, and the Cartography sheet was populated. Thanks very much!
0
FamousMortimerCommented:
Hi Andrea,

Sorry I didn't get back to you as I was on a mini vacation.  I am glad you were able to get it to work!  Thanks for the grade!

-FM
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.