Import new data into existing sheet to run macro on

tynawg

New Member
Joined
Oct 11, 2019
Messages
42
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

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,703
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

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

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

tynawg

New Member
Joined
Oct 11, 2019
Messages
42
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,456
Messages
5,596,234
Members
414,048
Latest member
wnied1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top