Selecting sheet to copy to based on cell value(Month name) from other sheet in other workbook

Question

I have a problem that I myself am not able to solve. Let me explain the problem: I use two workbooks: 1. formsbook 2.Logistics 2021 with 12 sheets from January to December.

In workbook 1 have made a form to fill in with some information. After completing this form and printing it I would like to copy all the information to workbook 2, at a specific sheet, but then all the information in one row. I want to use the month name that is in a cell in workbook 1 and use that monthname (cell F6) to select the according sheet in workbook 2

Until now I use the following code:

Sub Open_ExistingWorkbook()
'Open existing Workbook Logistics 2021 Workbook, specific sheet and then
'Writing Bol info to workbook Logistics 2021 Workbook and close it again
'Active Workbook is “Formsbook”

Dim BolNumber As String, DeliveryDate As Date, Time As String 

BolNumber = Range("K4")
DeliveryDate = Range("F6")
Time = Range("J29")

Workbooks.Open "C:\Users\BOL sheets\Logistics 2021 Workbook.xlsx"
Worksheets("April").Select
Worksheets("April").Range("A1").Select

If Worksheets("April").Range("A1").Offset(1, 0) <> "" Then
Worksheets("April").Range("A1").End(xlDown).Select
End If

ActiveCell.Offset(1, 0).Select
ActiveCell.Value = DeliveryDate
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Time
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = BolNumber
ActiveCell.Offset(0, 1).Select

ActiveWorkbook.Close Savechanges:=True
End Sub

Two links for you [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and [Finding the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) — Siddharth Rout, May 20 '21 at 11:04

score 0 · Answer 1 · answered May 20 '21 at 10:21

You cannot write value like your original code, it will slow down the macro execution and hard to track when result is not correct:

If Worksheets("April").Range("A1").Offset(1, 0) <> "" Then
Worksheets("April").Range("A1").End(xlDown).Select
End If

ActiveCell.Offset(1, 0).Select
ActiveCell.Value = DeliveryDate
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Time
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = BolNumber
ActiveCell.Offset(0, 1).Select

A better workaround should be:

Dim lastrow As Long
    
If Worksheets("April").Range("A1").Offset(1, 0) <> "" Then
    lastrow = Worksheets("April").Range("A1").End(xlDown).row + 1
Else
    lastrow = 1
End If

Worksheets("April").Cells(lastrow, 1).Value = DeliveryDate
Worksheets("April").Cells(lastrow, 2).Value = Time
Worksheets("April").Cells(lastrow, 3).Value = BolNumber

Selecting sheet to copy to based on cell value(Month name) from other sheet in other workbook

1 Answers1