Import new data into existing sheet to run macro on

[tynawg]

Hi,
I have a macro enabled workbook with a specific sheet I want to be able to import data from another wb and have the macro be able to run automatically. presently if I copy or move data it creates a new sheet so the pathway is changed for the macro.
At the moment I cant simply copy paste as the from sheet is a merged cell data set.
I have 2 workbooks open, one containing the new data. The other containing the macro and the tab i want to extract certain data to.
The issue is I am trying to get the code to be able to extract certain data and paste to the tab Imported data, next blank line. I need to do this continuously as the from data is essentially a new work order each time from client. So each time a new work order is received it needs to be put into the sheet where the macro runs then in the same wb extract the data to next blank line every time.
Hope I have made sense here.
Any help much appreciated.
Regards,
Wayne

Sub Import_SOR_Data2()
'
' Import_SOR_Data Macro
    st = Timer
    Application.ScreenUpdating = False
    Dim celA As Range, celB As Range, celC As Range
    Set ws1 = Sheets("ServiceOrder")
    Set ws2 = Sheets("Imported data")
    Set cel = ws2.Range("A1")
    Set celA = ws1.Range("BL3")
    Set celB = ws1.Range("BK3")
 
    If IsNumeric(celA) And celA <> "" Then cel = celA Else cel = celB
    ws2.Range("A2").Value = ws1.Range("R16").Value
    
    ws1.Select
    lastrow = Cells(40, "A").End(xlDown).Row
    
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Trade").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period

    ws2.Cells(3, "a").PasteSpecial
    
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Item Code").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    ws2.Cells(3, "b").PasteSpecial
    
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Qty/Hrs").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    ws2.Cells(3, "c").PasteSpecial
    
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Description").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    ws2.Cells(3, "d").PasteSpecial
   
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Location/Asset").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    ws2.Cells(3, "e").PasteSpecial
        
    For i = 1 To 5
        nr = Choose(i, 11, 11, 8, 55, 23)
        ws2.Columns(i).ColumnWidth = nr
    Next i
    
    ws2.Range("A3").CurrentRegion.Rows.AutoFit
    ws2.Select
    Cells(1, 1).Select
    Cells.Borders.LineStyle = xlLineStyleNone
    Application.ScreenUpdating = True
    Debug.Print Timer - st                                            '0.18 sec
End Sub

 

[Michael M]

At the moment I cant simply copy paste as the from sheet is a merged cell data set

Why can't you copy the merged cells ??
you could use something like

Range("[color=red]your merged cell[/color]").MergeArea.Copy

Change the text in red to the top left cell of the merged area

 

[tynawg]

Hi Michael M,
The data I want to copy from is always a new workbook sheet. I tried to select all copypaste but no good.
I think what I am asking is how do I set up to be able to import into the macro wb the new data but to an existing sheet so the macro still runs as no address changes or sheet name changes, if that makes sense.
Regards,
Wayne