Author: Oscar Cronquist Article last updated on February 14, 2021

save data to workbooks automatically 1

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

move data to workbooks

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.

move data to workbooks- flow chart
Back to top

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?

save data to workbooks automatically VB editor

  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.
Note, save your workbook with file extension *.xlsm to attach the code to your workbook.

Back to top

How to link macro to a button?

form control 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.
    form control button assign button
  8. Press with left mouse button on OK button to assign macro "Move_record_to_workbook" to the button.

resize and position 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