0

[![enter image description here][1]][1]I have two files. One get's updated and emailed daily. The other is the "Master" file[![enter image description here][2]][2] that I want to add the button/macro to. That is when running the Macro in the Master file I want to look through Column B on the daily updated file. If the Part Number is present in both that Column B (Sheet "Status") on the daily file and the Master file (Column H) the paste Columns C-N into the Master File (Sheet "XCHART") starting at Column AK

Sub CopyRange()
Dim a As Worksheet
Dim b As Worksheet
Dim rng As Range
'open the workbooks
Workbooks.Open "D:\OfficeDev\Excel\201510\Master.xlsx"
Set a = Workbooks("Master.xlsx").Worksheets("Sheet1")    
Workbooks.Open "D:\OfficeDev\Excel\201510\MasterBak.xlsx"
Set b = Workbooks("MasterBak.xlsx").Worksheets("Sheet1")
'loop the cells in column B
For r = 2 To a.UsedRange.Rows.Count
    If Trim(a.Cells(r, 2)) <> "" Then
        With b.Range("B:B")
            Set rng = .Find(What:=a.Cells(r, 2), _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
            If Not rng Is Nothing Then
                'write code to copy the cells
                Debug.Print a.Cells(r, 2)
            End If
        End With
    End If
Next

End Sub

Motorhead1308
  • 183
  • 2
  • 3
  • 17
  • First and foremost, all this `Active` and `.Select` stuff can really screw you up. See http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – Chrismas007 Oct 09 '15 at 14:52
  • there is a very large gap from you wrote that you want to accomplish and what your code is. I would suggest taking it step-by-step and coding each step and getting that step to work before moving on. For example: 1) With Master file open, find a reference to the daily file 2) find a reference to desired sheet 3) reference to column 4) Loop through each part number in that column 5) search for each part number in the master file in the lopop 6) If the part is found, copy the desired cells from daily file into the desired location of master file ... – Scott Holtzman Oct 09 '15 at 16:42
  • As pointed out by @Scott Holtzman Your code and description of you problem are not matching,If you can upload images of the Master file and daily updated file with the desired output shown, someone may be able to lend you a helping hand. – skkakkar Oct 09 '15 at 17:21
  • 1
    Your present code loops on line ~ If Cells(i, 1) = Date And Cells(i, 2) = “Sales” Then` If it is allowed to proceed further assuming if condition are met then it will write data on status sheet of workbook FAIMAIN.xlsx instead of MasterFile desired by you. If You are interested I can post a generic code which you can adopt to your situation. – skkakkar Oct 09 '15 at 18:10
  • Updated with pictures of the two files. I took someone else's code that was doing something similar to this that's probably why it's way off the mark. I'm not good with VBA at all. Any help would be greatly appreciated. – Motorhead1308 Oct 12 '15 at 10:49
  • @skkakkar yes some code would be great. I posted pictures of the two files to get an idea of what I'm looking at. – Motorhead1308 Oct 12 '15 at 12:55
  • I have seen your sample file and I am trying to workout something which is closer to your requirement. Pl let me have some time to get it. – skkakkar Oct 12 '15 at 15:10
  • No problem I appreciate the help !!! – Motorhead1308 Oct 12 '15 at 15:27
  • I have tried to match your requrements and have submitted an answer and have also uploaded the sample file for your tweaking. – skkakkar Oct 12 '15 at 18:11

1 Answers1

0

I have tried a solution using Dictionary Object which I think meets your requirement. Dictionay approach limits that part numbers are unique ie are not duplicates. I have also uploaded a sample file for your perusal and tweaking. This Sampl File

Please try This:

Sub CopyData()
    Dim j As Integer
    Dim k As Integer
    For j = 1 To 12
      Call CopyDataB(j) ' Call subroutine to transfer column values for matching Part No
    Next
End Sub

Sub CopyDataB(j)
    Dim Dic As Object, key As Variant, nCell As Range, i&
    Dim w1 As Worksheet, w2 As Worksheet
    Dim k As Integer
    Dim l As Integer
       Set Dic = CreateObject("Scripting.Dictionary") ' Create Dictionary Object Dictionary contains unique keys with data item
       Set w1 = Workbooks("FAIMAIN.xlsx").Sheets("Status") 'Workbook which is daily updated
       Set w2 = Workbooks("Master.xlsm").Sheets("XCHART") ' Master Workbook
       k = 29 ' set number to suit your column number requirements
       k = l + k ' Another integer variable added to enable proper looping of column offset
       i = w1.Cells.SpecialCells(xlCellTypeLastCell).Row

           For Each nCell In w1.Range("B2:N" & i)
               If Not Dic.exists(nCell.Value) Then
                 Dic.Add nCell.Value, nCell.Offset(, j).Value 'Dictionary adds Partno unique keys along with column data
               End If
           Next

      i = w2.Cells.SpecialCells(xlCellTypeLastCell).Row

          For Each nCell In w2.Range("H2:H" & i)
             For Each key In Dic
                If nCell.Value = key Then
                  nCell.Offset(, j + 28).Value = Dic(key) 'Dictionary key is matched and column value is written
                  l = l + 1
                End If
             Next
          Next
End Sub
skkakkar
  • 2,772
  • 2
  • 17
  • 30
  • WOW I didn't know you were going to put this much work into this haha. I can't open that file here at work with our fire walls. I'm trying to test out your code and don't see a reference for where the file resides. – Motorhead1308 Oct 13 '15 at 16:15
  • This will be files on different servers after it's all finished. Right now I'm just testing off a folder on my desktop with copies of the files. – Motorhead1308 Oct 13 '15 at 16:15
  • @Motorhead1308 Have you got the file with code in .xlsm format that is after pressing F11 you are able to view the VBA code. – skkakkar Oct 13 '15 at 16:28
  • I can't download the file here at work =( Macros are enabled if that's what you're asking and the file format is .xlsm – Motorhead1308 Oct 13 '15 at 16:38
  • The code errors out or highlights yellow at the "Set w1" section of the code. – Motorhead1308 Oct 13 '15 at 16:44
  • @Motorhead1308 I have uploaded archived version .rar. I suppose you can download it. I am also uploading to one drive in the archived format and will inform you the link. – skkakkar Oct 13 '15 at 16:47
  • @Motorhead1308 You also require "FAIMAIN.xlsx" in the same directory for the program to work and both files have to be open in the excel instance. I have also uploaded the other file. Refer Here – skkakkar Oct 13 '15 at 17:00