VBA workbook save data to new tab

[tynawg]

Hi,

I would like to change the destination to be a new tab rather than the imported data tab. I want to no longer have an imported data tab, rather a new tab each time I import or extract the data. We get multiple work orders or different locations. Once solved I will post the next part as a new question.

The reason is I will need to copy new work orders into the service order tab to then extract the data I need. So each time i copy a new work order i want to run the code, below, and extract the data and save it to a new tab, then delete anything remaining in the service order tab ready to paste a new work order for extraction.

Any help is greatly appreciated.

Regards,

Wayne

Sub Import_SOR_Data2()
'
' Import_SOR_Data Macro
    st = Timer
    Application.ScreenUpdating = False
    Dim celA As Range, celB As Range, cel 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

 

[davesexcel]

Straight from excel help

Set ws2= ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))
ws2.name="MySheetName"' you can also name it a specific cell ws2.name=ws1.range("BL3").value