automating drag & drop

rob171171 · #1 04-22-2016, 01:13 PM

Hi

I'm looking to see if the functionality exists within Excel formulas to automate drag & drop based on the date, or whether this is only possible through VBA / macros

I will ateempt to attach a file for the following mock up
Tab Status will be manually amended on a daily basis, based on project status. The ask here is how to automate the drag & drop process in tab Data

Hopefully the ask here is clarified.

Many Thanks in advance for any help

jeffreybrown · #2 04-22-2016, 04:41 PM

Quote:

Originally Posted by rob171171

The ask here is how to automate the drag & drop process in tab Data

Please clarify, drag & drop what? I see you say B to F, but what?

rob171171 · #3 04-22-2016, 11:24 PM

Hi
Each day (from tab Data) I have to select rows b through f and drag them down one row. I want the means to automate this

jeffreybrown · #4 04-23-2016, 05:18 AM

I'm guessing since you have two different formulas in the last two rows, you want both rows to go down one.

Try

Code:

Sub MoveDown()
    Dim LastRow As Long
    LastRow = Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row
    Sheets("Data").Range("B" & LastRow - 1).Resize(2, 5).Copy _
    Destination:=Sheets("Data").Range("B" & LastRow)
End Sub

rob171171 · #5 04-23-2016, 05:37 AM

Hi Jeffrey

The formula is the same in each row, its a simple countif of open & closed form Column A (Tab Status) and Column B (P1, P2 & P3 possibilities).
The values in A & B of Tab Status will vary as the project progresses and (hopefully) the number of items open will decrease with those closed increasing. Similarly the number of P1's, P2's & P3's will fluctuate on a daily basis.

The purpose of tab Data is to record this on a daily basis to show the burndown. I 'simply' want the row which corresponds to each day in tab data to automatically update.

That make sense??

jeffreybrown · #6 04-23-2016, 06:29 AM

See if this works for you

rob171171 · #7 04-23-2016, 06:39 AM

Thank you

Your code, once applied, updates all values, whereas I just want to update the value for the particular day, i.e. today I just want to update row 24 (23-Apr-16). Tomorrow I will want to just update row 25 (24-Apr-16), etc

jeffreybrown · #8 04-23-2016, 07:04 AM

Okay, this looks for today's date in column A and then pastes the formula in that row converting them to values.

I'm guessing in your original file you did not mean to keep all those formulas in B2:F23
as they would constantly change. So again, this code will copy the formula to the row with today's date and then convert to values.

Code:

Sub Rearrange()
    Dim LR As Long
    Dim Found As Range
    Dim MyFound As Long
    
    Const sFormula As String = "=COUNTIF(Status!$A$2:$A$#, B$1)"
    Const sFormula1 As String = "=COUNTIF(Status!$B$2:$B$#, D$1)"
    
    Set Found = Columns(1).Find(What:=Format(Date, "d-mmm-yy"), LookIn:=xlValues)
    
    MyFound = Found.Row - 1
    
    LR = Sheets("Status").Range("A" & Rows.Count).End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    With Sheets("Data").Range("B" & Found.Row).Resize(, 2)
        .Formula = Replace(sFormula, "#", LR)
        .Value = .Value
    End With
    
    With Sheets("Data").Range("D" & Found.Row).Resize(, 3)
        .Formula = Replace(sFormula1, "#", LR)
        .Value = .Value
    End With
    
    Application.ScreenUpdating = True
End Sub

rob171171 · #9 04-23-2016, 07:07 AM

Thats perfect Jeffrey. Works well

Thank you for your time and effort

jeffreybrown · #10 04-23-2016, 07:09 AM

You are very welcome. Glad it worked out for you.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Cannot drag and drop tasks	RandWald	Outlook	3	04-02-2016 06:45 AM
Drag and drop macro	Amanoo	PowerPoint	2	06-15-2012 04:01 AM
Drop and drag macro	Jamtart	PowerPoint	3	04-15-2012 11:05 PM
Drag and drop contacts	christer	Office	0	10-03-2011 09:54 AM
Drag & Drop problem	nickywave	Outlook	0	04-12-2011 01:40 AM