Excel tutorials

Copy Specific Data to a Closed Workbook after Selecting a Destination file name

In this Excel tutorial, I'll show you how to copy a certain cell range to a closed workbook whose name you choose from the Data Validation drop-down menu. For this Excel example, a VBA macro was used, which you can run via the VBA button. Copy the VBA code at the end of this tutorial to your Excel program - VBE to the Standard Module. (Source Internet network stackoverflow .com and all the credit goes to the author of VBA macros that I've adapted a little to this tutorial).

Notice the picture below the situation. In the cell range 'G2:I2' there are values (Data) we want to copy into a closed workbook. The 'A2' cell is located with the drop-down menu, where we choose from the name of the destination file in which we want to copy the cell range 'G2: I2'. By clicking on the VBA button, a VBA macro that starts copying data of a specified cell range to the specified file as closed Workbook.


VBA copying data to a closed Excel workbook

Closed destination workbook in Excel where we copy the data from an active workbook

The image below shows the first destination workbook that was closed by default. This closed workbook has the name 'destination1.xlsx'. This name can be selected from the drop-down menu in the 'A2' cell in our active workbook. After choosing the workbook name and start VBA macro from an active workbook, into this destined closed workbook the data will be copied to the first empty row.

Copy data to a closed workbook via VBA

Second, closed workbook where we copy the data of choice

In the image below, another destination workbook is presented. As in the previous description, this workbook is closed by default. In the same way, we copy the data from the active workbook.

The workbook by default is closed and we copy the data from the active workbook

VBA Macro to Copy Range into a Closed Excel Workbook
(Copy Specific Range and Paste to Closed Workbook)

Open your "master" workbook that will be active when copying. Copy this VBA macro into the standard module of your VBE for Excel. Save a workbook in *.xls or *.xlsm format.

Option Explicit
Public Sub CopyRangeToSpecificClosedWbk()
Dim mainWb As Workbook, mainWs As Worksheet, mainLr As Long, mainCol As Range
Dim thisWs As Worksheet, findTxt As String, foundCell As Variant
Dim fileName As String

Set thisWs = ThisWorkbook.Worksheets("Main") 'Name of the worksheet in the active workbook

Application.ScreenUpdating = False
On Error Resume Next 'expected error: File not exist, worksheet not exist

'Set mainWb = Workbooks.Open(fileName:"C:\Temp\destination.xlsx") 'the absolute path to the destination file

'destined paths merged with the name of the file from the A2 cell
'fileName = "C:\Temp\" & Range("A2").Text & ".xlsx"

'defined paths to the destination file from the A2 cell, the file format is xlsx
Set mainWb = Workbooks.Open(fileName:="C:\Temp\" & Range("A2").Text & ".xlsx")

If Err.Number = 0 Then 'If the destination file was not found
Set mainWs = mainWb.Worksheets("Sheet1") 'the worksheet name in the Workbook destination
If Err.Number > 0 Then Exit Sub 'If the worksheet was not found in the destination workbook
mainLr = mainWs.Cells(mainWs.Rows.Count, "A").End(xlUp).Row 'The last row in the "A" column on Sheet1 in the destination workbook
Set mainCol = mainWs.Range(mainWs.Cells(1, "A"), mainWs.Cells(mainLr, "A"))

findTxt = thisWs.Range("A2").Value 'A2 cell contains the name of the destination workbook
foundCell = Application.Match(findTxt, mainCol, 0) 'Search A column on the destination worksheet

If Not IsError(foundCell) Then 'Checking the existence of data in column A destination workbook on Sheet1
Set foundCell = mainWs.Cells(foundCell, "A") 'if NO, copy in the same row
Else
Set foundCell = mainWs.Cells(mainLr + 1, "A") 'if YES, copy in the first next empty row
End If

thisWs.Range("G2:I2").Copy 'the cell range that we copy to the destined closed workbook
foundCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
foundCell.Select
Application.CutCopyMode = False
mainWb.Close SaveChanges:=True
End If
Application.ScreenUpdating = True
End Sub

 

ATENTION!
AIf you are pressed by the VBA button to make sure that you have made a mistake or have accidentally pressed the button twice, then you have to know that after you start the VBA macros you have no way to go back (ie you do not have the UNDO command). To correct the mistake, you need to open the destination workbook and delete the data in the last row you accidentally copied your mistake.

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.