Results 1 to 6 of 6

Using Named Ranges to Transfer data from two worksheets

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-29-2013
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    143

    Using Named Ranges to Transfer data from two worksheets

    Attached is a sample of the problem that I'm working on. I'm trying to create a dashboard of my inventory system. The dashboard will display Sales for the day (category 1), Purchases that need to be made (Category 2), Purchase Orders that are to be expected (Cateogry 3), and Work in Process (Category 4). For this question, I'm only going to focus on category 2.

    I'm trying to transfer all the data from Worksheets("Purchasing") to the Dashboard under category 2. I'm trying to use named ranges to do this, because the range of each category will fluctuate as items are added/deleted. This is what I have so far:

    Option Explicit
    
    Sub purchPull()
    
    Dim Dashboard As Worksheet
    Dim Purchasing As Worksheet
    Dim PM As Range, D As Range, Rng As Range
    Dim purchName As Range
    
    Set Purchasing = Worksheets("Purchasing")
    Set Dashboard = Worksheets("Dashboard")
    
    
    ' Go through each Item in Purchasing and check to see if it's anywhere within the named range "PurchaseStart"
    ' In this case it should be "A8:A9" - as there is nothing in the dasboard yet
    For Each PM In Purchasing.Range(Purchasing.Cells(1, 1), Purchasing.Cells(Purchasing.Rows.Count, 1).End(xlUp))
        With Dashboard.Range("PurchaseStart", Dashboard.Cells(Dashboard.Rows.Count, 1))
            Set Rng = .Find(What:=PM.Offset(0, 1), _
                After:=.Cells(.Cells.Count), _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)
            If Not Rng Is Nothing Then
                ' Do nothing, as we don't want duplicates
            Else
                ' From the start of the named range, transfer data over - THIS IS THE PROBLEM AREA
                With Dashboard.Range("PurchaseStart", Dashboard.Cells(.Rows.Count, 1)).End(xlUp)
                    .Offset(1, 1) = PM.Offset(0, 0) ' Order Number
                    .Offset(1, 2) = PM.Offset(0, 1) ' SKU
                    .Offset(1, 3) = PM.Offset(0, 3) ' Qty
                    .Offset(1, 4) = PM.Offset(0, 4) ' Date
                End With
            End If
        End With
    Next
    
    End Sub
    For some reason the Range("PurchaseStart"), which is Cell $A$8, starts at A:1. I don't know how to only select the named range that I'm looking for. I added "End #" statements at the end of each row to signify a cutoff and hope to trick excel into only selecting a the range of the particular category. Any help would be greatly appreciated.
    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. Replies: 5
    Last Post: 12-17-2014, 12:23 AM
  2. [SOLVED] Named Ranges on Multlple Worksheets
    By ptmuldoon in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-28-2014, 11:15 PM
  3. Replies: 4
    Last Post: 02-06-2014, 11:13 PM
  4. Replies: 7
    Last Post: 05-27-2013, 01:40 PM
  5. Excel 2007 : sum named ranges in different worksheets
    By joethengineer in forum Excel General
    Replies: 1
    Last Post: 08-26-2011, 02:44 PM
  6. Accessing Named Ranges on different worksheets
    By gladst_j in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-10-2011, 09:55 AM
  7. Copy data in named ranges to a newer version of the same template to identical ranges
    By handstand in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-21-2006, 10:51 AM

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