Move data to workbooks

This article demonstrates a VBA macro that saves user input to a given workbook and worksheet programmatically. Macros are great for doing repetitive tasks, this saves you the steps to open a workbook, select a worksheet, save values to cells and lastly save the workbook and close.

This article demonstrates how to programmatically:

• Open a workbook based on a workbook name saved in a specific cell.
• Populate empty cells in the opened workbook with values.
• Create a new workbook if the workbook name doesn't exist.
• Insert a new worksheet if non-existing.
• Save changes

How the macro works

The Excel user enters a name, country, company, workbook, and worksheet name in cell range B3:F3. Then press the "Save record" button, see image above.

The macro tries to open the workbook in the same folder and copies cell range B3:F3 to the first empty row on the sheet. It then closes and saves the workbook.

If the workbook file can't be found it creates a new workbook and copies cell range B3:F3 to the first empty row on the sheet.

If the worksheet is not found the macros insert a new sheet and renames it. Then copies cell range B3:F3 to the first empty row.

Here is a flow chart.

VBA code

'Name macro
Sub Move_record_to_workbook()

'Save active workbook name to variable Wbs
Wbs = ActiveWorkbook.Name

'Save value in cell F3 to variable WSName
WSName = Range("F3").Value

'Save value in cell E3 to variable WBName
WBName = Range("E3").Value

'Enable error handling
On Error Resume Next

'Open workbook using path of active workbook and string in variable WBName
Workbooks.Open Filename:=Application.ActiveWorkbook.Path & "\" & WBName

'Check if an arror has occurred
If Err > 0 Then

    'Disable error handling
    On Error GoTo 0

    'Insert a new worksheet
    Workbooks.Add

    'Enable error handling
    On Error Resume Next

    'Activate worksheet WSName
    ActiveWorkbook.Worksheets(WSName).Activate

    'Check if an arror has occurred, if so insert a new worksheet
    If Err > 0 Then Worksheets.Add(After:=Worksheets(1)).Name = WSName

    'Disable error handling
    On Error GoTo 0
End If

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
With ActiveWorkbook.Worksheets(WSName)

    'Save row of first empty cell in column A to variable Lrow
    Lrow = .Range("A" & Rows.Count).End(xlUp).Row

    'Check if cell value is not equal to nothing, if so add 1 to variable Lrow
    If .Range("A" & Lrow).Value <> "" Then Lrow = Lrow + 1
End With

'Copy values in cell range B3:F3, Sheet1 to a row in worksheet WSName based on row number in variable Lrow
ActiveWorkbook.Worksheets(WSName).Range("A" & Lrow & ":C" & Lrow) = Workbooks(Wbs).Worksheets("Sheet1").Range("B3:F3").Value

'Save and close Workbook
ActiveWorkbook.Close SaveChanges:=True
End Sub

Back to top

Where to put the macro?

1. Press shortcut keys Alt+F11 to open the Visual Basic Editor.
2. Press with mouse on "Insert" on the top menu.
3. Press with left mouse button on "Module" to insert a new module to your workbook.
4. Paste VBA code to window.
5. Exit VB Editor and return to Excel.

Back to top

How to link macro to a button?

1. Go to tab "Developer". If tab "Developer" is missing.
2. Press with left mouse button on the "Insert" button on the ribbon.
3. Another menu shows up. Press with left mouse button on the button (Form Controls).
4. Press and hold with left mouse button on worksheet where you want to place the button.
5. Drag with mouse to size the button.
6. Release left mouse button.
7. A dialog box appears. Press with left mouse button on macro name "Move_record_to_workbook" to select it.
   
8. Press with left mouse button on OK button to assign macro "Move_record_to_workbook" to the button.

You can move and resize the button using the handles if you are not happy with the location or size.

Back to top

Get the Excel file

Move-data-to-workbooks.xlsm

Back to top

Files and folders category

Compare file names in two different folder locations and their sub folders
Today I would like to share a macro that compares the content in two different folders and their subfolders. It […]

Copy data from workbooks in folder and subfolders
I will in this article demonstrate a macro that automatically opens all workbooks in a folder and subfolders, one by […]

Filter duplicate files in a folder and subfolders
This is a follow up to my last post Compare file names in two different folder locations and their subfolders, the obvious […]

Excel categories

Comments (3)