Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-22-2016, 01:13 PM
rob171171 rob171171 is offline automating drag & drop Windows 7 64bit automating drag & drop Office 2010 64bit
Novice
automating drag & drop
 
Join Date: Sep 2013
Posts: 6
rob171171 is on a distinguished road
Default 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
Attached Files
File Type: xlsx Today.xlsx (12.8 KB, 18 views)
Reply With Quote
  #2  
Old 04-22-2016, 04:41 PM
jeffreybrown jeffreybrown is offline automating drag & drop Windows Vista automating drag & drop Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Quote:
Originally Posted by rob171171 View Post
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?
Reply With Quote
  #3  
Old 04-22-2016, 11:24 PM
rob171171 rob171171 is offline automating drag & drop Windows 7 64bit automating drag & drop Office 2010 64bit
Novice
automating drag & drop
 
Join Date: Sep 2013
Posts: 6
rob171171 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 04-23-2016, 05:18 AM
jeffreybrown jeffreybrown is offline automating drag & drop Windows Vista automating drag & drop Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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
Reply With Quote
  #5  
Old 04-23-2016, 05:37 AM
rob171171 rob171171 is offline automating drag & drop Windows 7 64bit automating drag & drop Office 2010 64bit
Novice
automating drag & drop
 
Join Date: Sep 2013
Posts: 6
rob171171 is on a distinguished road
Default

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??
Reply With Quote
  #6  
Old 04-23-2016, 06:29 AM
jeffreybrown jeffreybrown is offline automating drag & drop Windows Vista automating drag & drop Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

See if this works for you
Attached Files
File Type: xlsm Today (3).xlsm (18.6 KB, 11 views)
Reply With Quote
  #7  
Old 04-23-2016, 06:39 AM
rob171171 rob171171 is offline automating drag & drop Windows 7 64bit automating drag & drop Office 2010 64bit
Novice
automating drag & drop
 
Join Date: Sep 2013
Posts: 6
rob171171 is on a distinguished road
Default

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
Attached Files
File Type: xlsm Today (4).xlsm (21.3 KB, 9 views)
Reply With Quote
  #8  
Old 04-23-2016, 07:04 AM
jeffreybrown jeffreybrown is offline automating drag & drop Windows Vista automating drag & drop Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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
Reply With Quote
  #9  
Old 04-23-2016, 07:07 AM
rob171171 rob171171 is offline automating drag & drop Windows 7 64bit automating drag & drop Office 2010 64bit
Novice
automating drag & drop
 
Join Date: Sep 2013
Posts: 6
rob171171 is on a distinguished road
Default

Thats perfect Jeffrey. Works well

Thank you for your time and effort
Reply With Quote
  #10  
Old 04-23-2016, 07:09 AM
jeffreybrown jeffreybrown is offline automating drag & drop Windows Vista automating drag & drop Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

You are very welcome. Glad it worked out for you.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
automating drag & drop Cannot drag and drop tasks RandWald Outlook 3 04-02-2016 06:45 AM
automating drag & drop 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

Other Forums: Access Forums

All times are GMT -7. The time now is 05:44 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft