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]

sorry for the bump, is anyone able to assist?

 

[mumps]

Do you want to copy the entire "Summary" sheet or only part of it? If only part, which range(s)?

 

[michealhill]

Do you want to copy the entire "Summary" sheet or only part of it? If only part, which range(s)?

Hi Mumps,

The row range would be variable but the column range would always be A:M

 

[mumps]

Place this macro in a regular module in your reporting workbook and run it from there. Before running it, make sure you have a backup copy of the Batch Export workbook in a different folder just in case. Change the file extension (in red) to suit your needs.

Sub CopySheetData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet
    Set desWS = ThisWorkbook.Sheets("Batch Export")
    Workbooks.Open ThisWorkbook.Path & "\" & "Batch Export.xlsx"
    Sheets("Summary").UsedRange.Copy desWS.Cells(1, 1)
    ActiveWorkbook.Close False
    Kill ThisWorkbook.Path & "\" & "Batch Export.xlsx"
    Application.ScreenUpdating = True
End Sub

 

[michealhill]

Place this macro in a regular module in your reporting workbook and run it from there. Before running it, make sure you have a backup copy of the Batch Export workbook in a different folder just in case. Change the file extension (in red) to suit your needs.

Sub CopySheetData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet
    Set desWS = ThisWorkbook.Sheets("Batch Export")
    Workbooks.Open ThisWorkbook.Path & "\" & "Batch Export.xlsx"
    Sheets("Summary").UsedRange.Copy desWS.Cells(1, 1)
    ActiveWorkbook.Close False
    Kill ThisWorkbook.Path & "\" & "Batch Export.xlsx"
    Application.ScreenUpdating = True
End Sub

Thanks Mumps. I will try this on Monday and let you know how it went

 

[michealhill]

Place this macro in a regular module in your reporting workbook and run it from there. Before running it, make sure you have a backup copy of the Batch Export workbook in a different folder just in case. Change the file extension (in red) to suit your needs.

Sub CopySheetData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet
    Set desWS = ThisWorkbook.Sheets("Batch Export")
    Workbooks.Open ThisWorkbook.Path & "\" & "Batch Export.xlsx"
    Sheets("Summary").UsedRange.Copy desWS.Cells(1, 1)
    ActiveWorkbook.Close False
    Kill ThisWorkbook.Path & "\" & "Batch Export.xlsx"
    Application.ScreenUpdating = True
End Sub

hi mumps. i tried your code but got a subscript out of range error.

i have manage to piece together a code that somewhat works, however not fully as intended. see below:

Sub ImportBatch()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wbExp As Workbook
Dim wbRep As Workbook
Dim wsSumm As Worksheet
Dim wsBatch As Worksheet

Set wbRep = ThisWorkbook
Set wsBatch = wbRep.Sheets("Batch Export")
Set wbExp = Workbooks.Open("R:\Data Services\KPI Reports\Batch Export.xlsx")
Set wsSumm = wbExp.Sheets("Summary")

wsSumm.Copy wsBatch

wbExp.Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

However, the code above is pasting the copied data as a new sheet in the destination workbook, rather than in the wsSumm sheet specified.

Is anyone able to provide some guidance on how i can paste it into the designated sheet (ideally i would also like it to paste special as values)

Thanks in advance!

 

[mumps]

When you get the error, which line of code is highlighted when you click "Debug"?

 

[michealhill]

When you get the error, which line of code is highlighted when you click "Debug"?

the line with the error was:

Set desWS = ThisWorkbook.Sheets("Batch Export")

i have now got a code that works almost as intended:

Sub ImportBatch()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim wbExp As Workbook
Dim wbRep As Workbook
Dim wsSumm As Worksheet
Dim wsBatch As Worksheet

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")

wsBatch.Range("B:S").Value2 = wsSumm.Range("A:R").Value2
wbExp.Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

the only amendments i now need are:

the range being copied will be a set column range but variable row range, i need the code to be able to import the range that is being used
i also need the source worksheet to be deleted upon completion

any suggestions?

 

[mumps]

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