Author: Oscar Cronquist Article last updated on May 04, 2018

Macros are great for doing repetitive tasks. Two years ago I wrote a post about transferring data to worksheets. It is about automatically moving data to a worksheet you specify.

I got a comment from Andi:

is it possible for add values to different workbooks like add values to different sheets (vba)? thanks.

Yes, it is possible. Take a look at this sheet.

move data to workbooks

The user enters a name, country, company, workbook and sheet. Then press the "Save record" button.

The macro tries to open the workbook in the same folder and copies cell range B3:F3 to the first empty row on 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 sheet.

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

move data to workbooks- flow chart

Explaining the macro

Wbs = ActiveWorkbook.Name

Save active workbook's name to Wbs.

WSName = Range("F3").Value

Save worksheet name to WSName.

WBName = Range("E3").Value

Save workbook name you want to open to WBName.

On Error Resume Next
Workbooks.Open Filename:=Application.ActiveWorkbook.Path & "\" & WBName
If Err > 0 Then
On Error GoTo 0

Try to open workbook, if it fails create a new one.

On Error Resume Next
If Err > 0 Then Worksheets.Add(After:=Worksheets(1)).Name = WSName
On Error GoTo 0

Go to worksheet, if it fails create a new one and rename it.

With ActiveWorkbook.Worksheets(WSName)
Lrow = .Range("A" & Rows.Count).End(xlUp).Row
If .Range("A" & Lrow).Value <> "" Then Lrow = Lrow + 1
End With

Find first empty cell on column A.

ActiveWorkbook.Worksheets(WSName).Range("A" & Lrow & ":C" & Lrow) = Workbooks(Wbs).Worksheets("Sheet1").Range("B3:F3").Value

Copy values.

ActiveWorkbook.Close SaveChanges:=True

Save and close workbook.

Download excel *.xlsm file

Move data to workbooks.xlsm