Macro to copy, paste values, and remove blanks

Guido

New Member
Joined
Jul 17, 2007
Messages
7
I need a macro that will take the values that are in the far right cells and move them to an area on the left. the columns they will be pulling from are the IU & IV column starting with row 2 down to row 460. from there I need the values to be pasted into the D & E Columns starting at row 6. The two columns IU & IV are a date and a task for that date. When they are pasted into the columns D&E they will need to be sorted by dates (or just all of the blanks removed), with the soonest occurrence at the top. The reason for pasting values is because I have formulas pulling the tasks and dates off of another sheet. The last thing is that the macro needs to be triggered by the information in cell c2, when that cell is changed the formulas go to work and everything in cells IU & IV update. That is when I would like the macro to kick in and work the miracle.

I have been trying to build a colony of formulas that could do it, but I have given up, then I tried to make an array index it for me, but that wasn't working for me either. I am seriously lacking in my excel skills. Please HELP! Thank you.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
You didn't mention whether you wanted all columns sorted or columns D:E only.
This sorts columns D:E only :-

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect([C2], Target) Is Nothing Then
    Application.EnableEvents = False
    [D6:E464] = [IU2:IV460].Value
    [D6:E464].Sort Key1:=[D1], Order1:=xlAscending, Header:=xlNo
    Application.EnableEvents = True
End If
End Sub
 
Upvote 0
Try this:
Open the Visual Basic Editor (Alt + F11)
Select your project (workbook name) and expand the selection
Select Microsoft Excel Objects and expand the selection
DoubleClick the Sheet which you want this code to work on
In the code window paste this code:


Code:
Private Sub Worksheet_Change(ByVal Target As Range)

'when C2 changes
If Not Intersect(Target, Range("C2")) Is Nothing Then

    'Escape hatch
    ans = MsgBox("Update Dates and Tasks now?", vbYesNo)
    If ans <> vbYes Then Exit Sub

    'copy data in columns IU and IV
    Range("IU2:IV460").Copy
    'paste values and date formats at D6
    Range("D6").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    'Sort ascending
    Range("D6:E464").Sort Key1:=Range("D6"), Order1:=xlAscending, Header:=xlNo
End If

End Sub
 
Upvote 0
Boller

Thanks for your response, however the code you suggested only sorts, and i need it to do alot more, you were correct about sorting columns D & E only though, and also from row 6 down to 464 only.

I appreciate it.
 
Upvote 0
Alexander,

Your code looks like it would do what I need, however, something has happened to my Excel and I can't make it run. I have my VB editor open, the code copied to the worksheet in question, saved, I even made sure that the Excel and Office Libraries were referenced. It won't do anything. I can hit Alt+F8 to bring up the list of macros in the workbook or all open workbooks and anything I save in the workbook does not show up. I am confused, also everytime I open Excel it always opens the Personal.xls workbook, which is annoying. If you or anyone could help me with this problem I would certainly appreciate it. Also I could tell you if your code works too.

Thanks
 
Upvote 0
1) Check your macro security:

Tools | Options
Security (tab)
Macro security (button)

Set macro security level to medium

2) Also, When the Personal workbook is open ... go to
Window | Hide

This will hide the workbook. Now close Excel (the whole program) and say yes to save changes (so that it will always be a hidden workbook, on open as well). You can also save these changes in the VBE. Or you can delete the file if desired.

Does this help?
 
Upvote 0
Re: Boller

Thanks for your response, however the code you suggested only sorts, and i need it to do alot more, you were correct about sorting columns D & E only though, and also from row 6 down to 464 only.

I appreciate it.

You need it to do a lot more ?
The code does what you requested. That is, copies from IU:IV and pastes to D:E, then sorts D:E.

It does the same thing as the code posted later by Alexander Barnes.

If the Worksheet_Change procedure is not working, try running this :-

Code:
Sub EnableEv()
Application.EnableEvents=True
End Sub
 
Upvote 0
Agreed with Boller - I was working on my solution as he was posting his. I believe the only real difference in our solutions is that I include a message prior to making changes on the worksheet (we accomplish the copying of values by different methods - and I appreciate the simplicity of Boller's quite a lot).

Let us know how you fare with the suggestions to get everything working properly.

Regards.
 
Upvote 0
Alexander, Boller,

First thing, apologies to Boller. While I could not get Alexanders code to work yours would but it was only sorting columns D&E. Now after studying your code and I see where your code was supposed to do everything I asked.

After Changing my Macro Security to medium, and hiding that annoying Personal.xls, I tried both codes again, and the same thing Bollers code only sorted D&E and Alexander your code would still not work at all. Also it would not retain either of them, when I would close Excel and reopen the file i would have to come back to this post and copy and paste again.

Finally I did what I should have done at first when the codes wouldn't run correctly... the old magical restart the whole computer trick. Worked like a charm.

Thank you both for all your help.

Guido
The one who should probably buy an Excel book
 
Upvote 0
One more thing, I have changed the layout of the worksheet a little, and now I have a combo box to where i can select the values from a dropdown list. Now the Combo Box changes the value in C2 and the code doesn't work anymore, because i am not physically changing the cell myself. Is this a simple fix?
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top