Results 1 to 6 of 6

Values to be routed from one sheet to other sheets

Threaded View

  1. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,691

    Re: Values to be routed from one sheet to other sheets

    See attached.

    Sub Transfer_data()
    
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    Dim outcol As Integer
    
    Dim var As Variant
    
    
    Set ws1 = Worksheets("Rej Rep")
    
    ws_Name = ws1.Range("B4")
    Set ws2 = Worksheets(ws_Name)   ' Set worksheet name
    '
    '  Determine output column bassed on date in "Rej Rep"  E3
    '
    var = Application.Match(ws1.Range("E3"), Worksheets(ws_Name).Range("A4:AE4"), 0)
    outcol = var
    '
    '  Copy data from "Rej Rep"
    '
    
    ws1.Range("C8:C30").Copy Destination:=ws2.Cells(5, outcol)
    ws1.Range("C7").Copy Destination:=ws2.Cells(29, outcol)
    
    
    ws_Name = ws1.Range("E4")
    Set ws2 = Worksheets(ws_Name)
    
    ws1.Range("F8:F30").Copy Destination:=ws2.Cells(5, outcol)
    ws1.Range("F7").Copy Destination:=ws2.Cells(29, outcol)
    
    
    ws_Name = "PC GF"
    Set ws2 = Worksheets(ws_Name)
    
    ws1.Range("I38:I49").Copy Destination:=ws2.Cells(4, outcol)
    ws1.Range("I37").Copy Destination:=ws2.Cells(17, outcol)
    
    ws_Name = "PC SF"
    Set ws2 = Worksheets(ws_Name)
    
    ws1.Range("L38:L49").Copy Destination:=ws2.Cells(4, outcol)
    ws1.Range("l37").Copy Destination:=ws2.Cells(17, outcol)
    
    ws_Name = "GF LC"
    Set ws2 = Worksheets(ws_Name)
    
    ws1.Range("O38:O49").Copy Destination:=ws2.Cells(4, outcol)
    ws1.Range("O37").Copy Destination:=ws2.Cells(17, outcol)
    
    
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sending Routed Worksheet as a 1993-2003 Doc
    By walke145 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-02-2014, 11:01 AM
  2. Replies: 3
    Last Post: 01-14-2014, 11:59 AM
  3. Sum values from different sheets to summary sheet
    By puttu in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-18-2013, 10:49 AM
  4. Comparing two sheets and overlaping same values of sheet 1 in sheet 2
    By littledic in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-15-2011, 09:24 AM
  5. Adding values from sheets to sheet
    By psycheus in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-01-2007, 10:57 AM
  6. Change Routing Message in Outlook when Excel File is Routed
    By Mike R. in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-19-2005, 12:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1