VBA Solution Required - Copy data from a closed workbook, paste into sheet in active workbook, delete closed workbook

[michealhill]

Hi,

I'm looking for a code that can be run from a reporting workbook that I am building, which will copy data from a closed workbook, paste into a sheet in my reporting workbook and then delete the source file for the copied data once complete.

Some additional detail that might help:

• the closed workbook will be called Batch Export, it will have one sheet called Summary
• the closed workbook will be located in the same network folder location as the reporting workbook
• the sheet on the reporting workbook will be called Batch Export

Is anyone able to help with a solution? Any assistance would be greatly appreciated!

Thanks in advance,

Micheal

 

[michealhill]

Try this macro. I have moved the Application.DisplayAlerts lines to prevent a pop-up when the Summary sheet is deleted. I have also added "True" to the line that closes the workbook so that it is saved.

Sub ImportBatch()
    Application.ScreenUpdating = False
    Dim wbExp As Workbook, wbRep As Workbook, wsSumm As Worksheet, wsBatch As Worksheet, LastRow As Long
    Set wbRep = ThisWorkbook
    Set wsBatch = wbRep.Sheets("Batch Export LI")
    Set wbExp = Workbooks.Open("R:\Data Services\KPI Reports\Batch Export LI.xlsx")
    Set wsSumm = wbExp.Sheets("Summary")
    With wsSumm
        LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        wsBatch.Range("B1:S" & LastRow).Value2 = .Range("A1:R" & LastRow).Value2
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
    wbExp.Close True
    Application.ScreenUpdating = True
End Sub

hi mumps. worked on the code through most of the morning and finally got it working. resulting code is:

Sub ImportBatch()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim wbExp1 As Workbook
    Dim wbExp2 As Workbook
    Dim wbRep As Workbook
    Dim wsSumm1 As Worksheet
    Dim wsSumm2 As Worksheet
    Dim wsBatch1 As Worksheet
    Dim wsBatch2 As Worksheet
    Dim LastRow1 As Long
    Dim LastRow2 As Long

    Set wbRep = ThisWorkbook
    Set wsBatch1 = wbRep.Sheets("Batch Export LI")
    Set wsBatch2 = wbRep.Sheets("Batch Export LH")
    Set wbExp1 = Workbooks.Open("R:\Data Services\KPI Reports\Batch Export LI.xlsx")
    Set wbExp2 = Workbooks.Open("R:\Data Services\KPI Reports\Batch Export LH.xlsx")
    Set wsSumm1 = wbExp1.Sheets("Summary")
    Set wsSumm2 = wbExp2.Sheets("Summary")
    
    Sheets("Batch Export LI").Visible = True
    Sheets("Batch Export LH").Visible = True

        LastRow1 = wsSumm1.Cells.SpecialCells(xlCellTypeLastCell).Row
        LastRow2 = wsSumm2.Cells.SpecialCells(xlCellTypeLastCell).Row

        wsBatch1.Range("B1:S" & LastRow1).Value2 = wsSumm1.Range("A1:R" & LastRow1).Value2
        wsBatch2.Range("B1:S" & LastRow2).Value2 = wsSumm2.Range("A1:R" & LastRow2).Value2

        wbExp1.Close
        wbExp2.Close

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    Sheets("Batch Export LI").Visible = False
    Sheets("Batch Export LH").Visible = False

    Kill "R:\Data Services\KPI Reports\Batch Export LI.xlsx"
    Kill "R:\Data Services\KPI Reports\Batch Export LH.xlsx"

End Sub

thanks for your suggestions on this. very much appreciated!

 

[mumps]

Glad it worked out.

 

[Mark_365]

Hi,

I'm looking for a code that can be run from a reporting workbook that I am building, which will copy data from a closed workbook, paste into a sheet in my reporting workbook and then delete the source file for the copied data once complete.

Some additional detail that might help:

• the closed workbook will be called Batch Export, it will have one sheet called Summary
• the closed workbook will be located in the same network folder location as the reporting workbook
• the sheet on the reporting workbook will be called Batch Export

Is anyone able to help with a solution? Any assistance would be greatly appreciated!

Thanks in advance,

Micheal

Can you help me too? I am looking for a macro that can copy the first sheet (Sheet1) of an unopened worksheet to the certain sheet of an active workbook. The path containing the name and extension of the file (.xlsx, .xls, .xlsm, .xlsb, .csv) is provided in the a cell.

I would like to do this for two sheets by just one click if possible. Meaning two paths with name and extension are provided in the two cells and once a button is click the first sheets of those excel files will be pasted to two separate sheets (Sheet3 and Sheet4) of the active workbook.

I hope you can help me this. Big thanks!

 

[mumps]

Hello and welcome to the Forum. Please start your own new thread and include a link to this thread if you found it useful.