#1
|
|||
|
|||
automating drag & drop
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 |
#2
|
|||
|
|||
Please clarify, drag & drop what? I see you say B to F, but what?
|
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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?? |
#6
|
|||
|
|||
See if this works for you
|
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
Thats perfect Jeffrey. Works well
Thank you for your time and effort |
#10
|
|||
|
|||
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 |