selecting monthly tab based on input date

gsw721 · Aug 15th 2021

Hello,

I am not a code writer! i was able to write a few macros due to google/youtube knowledge. Here is the long story short:

In my office, we use an excel spreadsheet to keep track of dates, times, addresses, comments, etc. This is broken down into monthly tabs. The same information that goes on the main log has to also be entered onto a personal log and a back up log. Basically entering the same information three times.

I wrote a few macros that allow the user to input the information. It then places the information in the appropriate cells. At the end it copies the row of cells, opens the main spreadsheet, and pasts the row in the last line.

Here is the problem. Each month, I have to edit the macro to open the current months tab. Is there a way to have the macro select the correct tab based on the date that the user input?

Process:
1. message box asks for the date of the transaction MM/DD (8/1)
2. Excel places the date into the cell as Aug-1

I need the macro to be able to open the main spreadsheet and then open the AUG tab so it can paste the content in that line.
Clear as mud?

If i need to send the files I can.

thanks for the help
G

gsw721 · Aug 15th 2021

The code written is : Set wsTO = workbooks("2021 FIB Log.xlsm").Worksheets("AUG")

I need the AUG to be automatically changed based on the date inputted.

NoSparks · Aug 15th 2021

Quote

2. Excel places the date into the cell as Aug-1

will Left(the cell.Text, 3) get what you're after ?

gsw721 · Aug 15th 2021

Quote from NoSparks

will Left(the cell.Text, 3) get what you're after ?

i dont know... i have never seen that before and have no idea where to put it into the code line.

Any idea where and how it would fit into - Set wsTO = workbooks("2021 FIB Log.xlsm").Worksheets("AUG")

also, i was mistaken. the cell formats the date as 1-aug. so i guess there is a "right" command too?

Im a novice at VBA. if there isnt a video how to use it, it takes me much longer to figure it out.

Thanks for the help

G

**royUK** · Aug 16th 2021

Attach an example workbook.

gsw721 · Aug 16th 2021

here are the programs.

trial data entry is the program used to ask questions. i already have the line filled so the query is not needed. The macro in question is FIB_log.

**royUK** · Aug 16th 2021

Maybe something like this

Code

Sub FIB_Log()
Dim wb As Workbook
Dim ThisMonth As String, res


'opens file
Workbooks.Open FileName:="\\fire-fp3\FI-Restricted\FIB Incident Logs\2021 FIB Incident Log.xlsm"      'network locations




Dim wsFROM As Worksheet
Dim wsTO As Worksheet
Dim FROMlastrow As Long
Dim TOlastrow As Long


res = Application.InputBox("Enter A Date")


If Not IsDate(res) Then Exit Sub
ThisMonth = MonthName(Month(CDate(res)))


' Set variables for worksheet names
Set wsFROM = Workbooks("trial Data Entry.xlsm").Worksheets("sheet1")                      'from this log
Set wsTO = Workbooks("2021 FIB Incident Log.xlsm").Worksheets(Left(ThisMonth, 3))   '      !!!Change month here!!!  Sheets(Format(Now,"mmm")).Activate


'find last used row from the from page based on data in the "A" column
FROMlastrow = wsFROM.Cells(wsFROM.Rows.Count, "a").End(xlUp).Row


'find last used row on the TO page based on data in the "A" column
TOlastrow = wsTO.Cells(wsTO.Rows.Count, "A").End(xlUp).Offset(1).Row


'3. Copy & Paste Data  "a2:d" is the starting cell and ending cell of data to be copied
wsFROM.Range("A2:o2").Copy _
wsTO.Range("a" & TOlastrow) '& FROMlastrow




'ActiveWorkbook.Save
'Application.Wait (Now + 0.00005)
'ActiveWorkbook.Close






End Sub

Display More

gsw721 · Aug 16th 2021

Quote from gsw721

Hello,

I am not a code writer! i was able to write a few macros due to google/youtube knowledge. Here is the long story short:

In my office, we use an excel spreadsheet to keep track of dates, times, addresses, comments, etc. This is broken down into monthly tabs. The same information that goes on the main log has to also be entered onto a personal log and a back up log. Basically entering the same information three times.

I wrote a few macros that allow the user to input the information. It then places the information in the appropriate cells. At the end it copies the row of cells, opens the main spreadsheet, and pasts the row in the last line.

Here is the problem. Each month, I have to edit the macro to open the current months tab. Is there a way to have the macro select the correct tab based on the date that the user input?

Process:
1. message box asks for the date of the transaction MM/DD (8/1)
2. Excel places the date into the cell as Aug-1

I need the macro to be able to open the main spreadsheet and then open the AUG tab so it can paste the content in that line.
Clear as mud?

If i need to send the files I can.

thanks for the help
G

Display More

**royUK** · Aug 17th 2021

Did that work for you?

gsw721 · Aug 17th 2021

Quote from royUK

Did that work for you?

I was given a line of code from another forum that accomplished what it needed to do. I have not tried yours. but thank you for the help.

their code was

Set wsTO = Workbooks("2021 FIB Incident Log.xlsm").Worksheets(Format(wsFROM.[A2], "mmm"))

**royUK** · Aug 18th 2021

Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the URL from the address bar in your browser) to the cross-post. We are here to help so help us help you!

Read this to understand why we ask you to do this

https://www.excelguru.ca/content.php?184

selecting monthly tab based on input date

Participate now!

Share

Users Viewing This Thread