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:
Yes, it is possible. Take a look at this sheet.
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.
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 Workbooks.Add
Try to open workbook, if it fails create a new one.
On Error Resume Next ActiveWorkbook.Worksheets(WSName).Activate 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
Save and close workbook.