Importing file as temp and copy paste info into main file!!! HELP PLEASE

**cory0789** · 01-02-2015, 04:00 PM

Ok, I have a file that gets sent to me in an email... the example file attached is examplefile1... I also have the main file that I work with being examplefile2 that is attached... What I need is a macro that I can run in examplefile2 that allows me to select the examplefile1 open it as a temp file or whatever is needed to copy the order number column and paste it into the main exapmlefile2 location...

To try to explain again, examplefile1 is emailed to me, I open examplefile2 and run a macro in which it allows me to select the emailed file that I have saved somewhere and pulls the order numbers from it and pastes them into examplefile2 in the proper area.

PLEASE NOTE THESE EXAMPLE FILES ARE EXTREMELY SIMPLE COMPARED TO WHAT I AM WORKING WITH... I JUST NEED THE SIMPLE IDEA OF HOW TO DO IT AND I CAN GET IT TRANSLATED TO MY ACTUAL WORKSHEETS...

Thanks,examplefile1.xlsx examplefile2.xlsx

TIA!!!

**natefarm** · 01-02-2015, 05:14 PM

This is a most basic starter solution. It assumes that your rows between the two workbooks match up, and all you want to do is copy column G from one sheet to the other, which is not likely the case.

Option Explicit
Dim filename As String

Sub GetData()
    filename = Application.GetOpenFilename("Excel Files (*.xls*, *.xls*", , "Select file to open")
    If filename = "False" Then
        Exit Sub
    End If
    
    Workbooks.Open (filename)
    ActiveSheet.Columns(7).Copy Destination:=ThisWorkbook.Sheets("Sheet1").Columns(7)
    ActiveWorkbook.Close (False)
End Sub

**cory0789** · 01-02-2015, 05:40 PM

SLAUGHTER W ORDER NUM.xlsx Submitted Orders.xlsx

Ok, here is a better example of what I am trying to do... So the slaughter order page is the file I would be saving from my email. Once saved I will open the submitted orders file and run a macro that needs to do the following...

Open the slaughter order temporarily to retrieve the plant name which is in cell M15 (ALWAYS) and search in the submitted orders file for that plant name as well as compare product on the slaughter sheet that DOES have an order number with the same product at that plant on the submitted order page with NO order number... then the order number will be pasted there...

For example.

The slaughter sheet has two 9000987 products in which only 1 of them has an order number... that means there will only be 1 in the submitted order file without an order number... so I need the macro to Say ok Find "Slaughter Gas Plant" where B(SAME ROW THAT SLAUGHTER GAS PLANT WAS FOUND) = Product from first file take (9000987) for example AND "P(SAME ROW THAT SLAUGHTER GAS PLANT WAS FOUND)" = "" then Copy from order sheet "O(whatever row the first order number is found)" and PASTE into "P(Same ROW THAT WAS RETURNED IN SEARCH)...

This will always be a different plant name and have several different products on the list...

Thanks for all the help

**cory0789** · 01-02-2015, 05:21 PM

That gets me started I now need someone to help me figure out I need vba to search for say Apples and where Apples has Order Number of NOTHING OR "" and Product = product in that same row copy the order number from first file and paste it into the second file...

Hope that makes sense...

**natefarm** · 01-02-2015, 05:32 PM

See if I got it right.

Option Explicit
Dim FileName As String, rw As Long, FindProd As Range

Sub GetData()
    FileName = Application.GetOpenFilename("Excel Files (*.xls*, *.xls*", , "Select file to open")
    If FileName = "False" Then
        Exit Sub
    End If
    
    Workbooks.Open (FileName)
    With ThisWorkbook.Sheets("Sheet1")
        rw = 2
        Do Until .Cells(rw, 3).Value = "" ' loop throug products in main file sheet
            Set FindProd = Cells.Find(what:=.Cells(rw, 3).Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not FindProd Is Nothing Then
                .Cells(rw, 7).Value = Cells(FindProd.Row, 7).Value
            End If
            rw = rw + 1
        Loop
    End With
    ActiveWorkbook.Close (False)
End Sub

**natefarm** · 01-02-2015, 06:01 PM

Is the Submitted Orders sheet sorted by Plant Name. In other words, are the plants grouped together, or are the scattered?

**cory0789** · 01-02-2015, 10:18 PM

scattered... but they can be sorted if needed.

**cory0789** · 01-03-2015, 02:44 AM

This is a failure of attempting to get it to do what I want... someone please help me figure it out...
I am terrible at the find function please use it to solve this it has to be much easier than the cluster I have below!!!

Thanks again

        BaseWks.Activate
         x = 30
         For m = 2 To 99
         
         BaseWks.Activate
         If Range("A" & m).Value = PlantName And Range("P" & m).Value = Empty Then
         mybook.Activate
         If Range("E" & x).Value > 0 Then
         Product = mybook.Worksheets(1).Range("A" & x).Value
         Do
            BaseWks.Activate
            If Product = Range("B" & m).Value Then
            mybook.Activate
            OrderNumber = mybook.Worksheets(1).Range("O" & x).Value
            BaseWks.Activate
            Range("P" & m).Value = OrderNumber
            Exit Do
            Else
            m = m + 1
            x = x + 1
            End If
         
        Loop Until x = 55

          x = x + 1
            
         Else
         End If
         
         Else
         End If
         Next m

**natefarm** · 01-05-2015, 02:01 PM

Give this a try.

Option Explicit
Dim FileName As String, Plant As String
Dim SltrRw As Long, Sub1stRw As Long, SubRw As Long

Sub GetData()
    Sheets("Submitted Orders").Select
    Range("A2").Select
    Cells.Sort key1:=Range("A2"), order1:=xlAscending, Header:=xlYes ' Sort by Plant Name

    FileName = Application.GetOpenFilename("Excel Files (*.xls*, *.xls*", , "Select file to open")
    If FileName = "False" Then
        Exit Sub
    End If

    Workbooks.Open (FileName)
    Plant = Range("M15").Value
    With ThisWorkbook.Sheets("Submitted Orders")
        Sub1stRw = 0
        On Error Resume Next
        Sub1stRw = .Cells.Find(what:=Plant, LookIn:=xlValues, lookat:=xlWhole).Row
        On Error GoTo 0
        If Sub1stRw = 0 Then
            MsgBox Plant & " not found in " & .Name
            Exit Sub
        End If

        SltrRw = Range("A1").End(xlDown).Row + 1
        Do Until Cells(SltrRw, 1).Value = "" ' loop through products in Slaughter Process sheet
            SubRw = Sub1stRw
            If Cells(SltrRw, 15).Value <> "" Then ' it has an order #
                Do Until .Cells(SubRw, 2).Value = Cells(SltrRw, 1).Value Or .Cells(SubRw, 1).Value <> Plant
                    SubRw = SubRw + 1
                Loop
                If .Cells(SubRw, 2).Value = Cells(SltrRw, 1).Value Then ' found the matching product
                    .Cells(SubRw, 16).Value = Cells(SltrRw, 15).Value
                End If
            End If
            SltrRw = SltrRw + 1
        Loop
    End With
    ActiveWorkbook.Close (False)
End Sub

**natefarm** · 01-05-2015, 01:25 PM

I had code ready for you Friday and was waiting for your response about the sort, but I lost the code over the weekend, so I'm starting over. I'll get back with you shortly.

Importing file as temp and copy paste info into main file!!! HELP PLEASE

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Importing file as temp and copy paste info into main file!!! HELP PLEASE

Re: Importing file as temp and copy paste info into main file!!! HELP PLEASE

Re: Importing file as temp and copy paste info into main file!!! HELP PLEASE

Re: Importing file as temp and copy paste info into main file!!! HELP PLEASE

Re: Importing file as temp and copy paste info into main file!!! HELP PLEASE

Re: Importing file as temp and copy paste info into main file!!! HELP PLEASE

Re: Importing file as temp and copy paste info into main file!!! HELP PLEASE

Re: Importing file as temp and copy paste info into main file!!! HELP PLEASE

Re: Importing file as temp and copy paste info into main file!!! HELP PLEASE

Re: Importing file as temp and copy paste info into main file!!! HELP PLEASE

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copy same range in many excel files and paste into a main file

Macro to find a partial name of file, copy info and paste it in another workbook

VBA code to open many files, copy info, move file to new folder, paste info in second work

Macro to copy range from temp file to open file

Open 2nd file(CSV) from cell reference, copy columns to main file & close 2nd file

Bookmarks

Bookmarks

Posting Permissions