Solved

Sending a value to two places.

Posted on 2011-09-14
2
239 Views
Last Modified: 2012-05-12
I use the following code to throw a value in cell D8 of the MDL - Summary sheet tab.

How would I edit this to throw the same value to another sheet tab cell?  Say Sheet1, cell B9?

Please advise and thanks.  -R-
Sub MDL_GetCompletedOrdersTodayMinusOneDay()
'On Format tab only
Dim rgFilter As Range, rgTarg As Range
Set rgTarg = Worksheets("MDL - Summary").Range("D8")  'Record count goes here
Sheets("Format").Select

With Worksheets("Format")       'Worksheet to be filtered
    Set rgFilter = .Range("A1")     'First header label in range to be filtered
    Set rgFilter = Intersect(.UsedRange, rgFilter.Resize(1, 100).EntireColumn)   'All the rows of data
    
    rgTarg.Formula = "=SUBTOTAL(3,'" & .Name & "'!" & rgFilter.Columns(3).Address & ")-1"
    With rgFilter
        .AutoFilter
        .AutoFilter Field:=7, Criteria1:="SPCLMDL"
        
        .AutoFilter Field:=10, Criteria1:="=CNF  LKD  REL", Operator:=xlOr, Criteria2:="=CNF  REL"
        .AutoFilter Field:=22, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Date - 1, "m/d/yyyy"))
'        .AutoFilter Field:=22, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Date, "m/d/yyyy"))
                
        rgTarg.Formula = rgTarg.Value
'        .AutoFilter     'Turn filters off
    End With
End With

Sheets("MDL - Summary").Select
'    Range("C4").Select
    
End Sub

Open in new window

0
Comment
Question by:RWayneH
2 Comments
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 500 total points
ID: 36539131
The code in line 23 will add the same value to the cell you suggest.  Hopefully it's fairly clear how you would change this to another cell.

Thanks

Stuart

Sub MDL_GetCompletedOrdersTodayMinusOneDay()
'On Format tab only
Dim rgFilter As Range, rgTarg As Range
Set rgTarg = Worksheets("MDL - Summary").Range("D8")  'Record count goes here
Sheets("Format").Select

With Worksheets("Format")       'Worksheet to be filtered
    Set rgFilter = .Range("A1")     'First header label in range to be filtered
    Set rgFilter = Intersect(.UsedRange, rgFilter.Resize(1, 100).EntireColumn)   'All the rows of data
    
    rgTarg.Formula = "=SUBTOTAL(3,'" & .Name & "'!" & rgFilter.Columns(3).Address & ")-1"
    With rgFilter
        .AutoFilter
        .AutoFilter Field:=7, Criteria1:="SPCLMDL"
        
        .AutoFilter Field:=10, Criteria1:="=CNF  LKD  REL", Operator:=xlOr, Criteria2:="=CNF  REL"
        .AutoFilter Field:=22, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Date - 1, "m/d/yyyy"))
'        .AutoFilter Field:=22, Operator:=xlFilterValues, Criteria2:=Array(2, Format(Date, "m/d/yyyy"))
                
        rgTarg.Formula = rgTarg.Value

' This adds the same value to another cell
        Worksheets("Sheet1").Range("B9").Value = rgTarg.Value

'        .AutoFilter     'Turn filters off
    End With
End With

Sheets("MDL - Summary").Select
'    Range("C4").Select
    
End Sub

Open in new window

0
 

Author Closing Comment

by:RWayneH
ID: 36539238
Thank You!!  -R-
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now