So I have Monthly sales sheets that import my cash register data int
them. I wanted to set them up to do everything without being there.
So I have my task manager open excel at 9:30pm everyday and it runs th
macro to import the data into the correct day of the month. Here is th
workbook open macro-
Code
-------------------
Private Sub Workbook_Open()
Dim dTime As Date
dTime = Time
If dTime = TimeValue("9:30 PM") And _
dTime < TimeValue("9:40 PM") Then
ImportData
End If
End Sub[\code]

This is in my July spreadsheet only. So is there a way to make it know which month spreadsheet to open on the 1st of the month? So come August 1st it will automatically open the August workbook and input the data for the first day? By using the date? Thanks so much

--
buzzharle
-----------------------------------------------------------------------
buzzharley's Profile: http://www.excelforum.com/member.php...fo&userid=3588
View this thread: http://www.excelforum.com/showthread.php?threadid=55947

If DatePart("m", Date) = "1" Then
Sheets("Jan").select ' or however you have named the January sheet
elseif DatePart("m",Date) = "2" then
Sheets("Feb").select
'through the twelve months
end if

Best wishes,

Jim



"buzzharley" wrote:


So I have Monthly sales sheets that import my cash register data into
them. I wanted to set them up to do everything without being there.
So I have my task manager open excel at 9:30pm everyday and it runs the
macro to import the data into the correct day of the month. Here is the
workbook open macro-
Code:
--------------------
Private Sub Workbook_Open()
Dim dTime As Date
dTime = Time
If dTime = TimeValue("9:30 PM") And _
dTime < TimeValue("9:40 PM") Then
ImportData
End If
End Sub[\code]

This is in my July spreadsheet only. So is there a way to make it know which month spreadsheet to open on the 1st of the month? So come August 1st it will automatically open the August workbook and input the data for the first day? By using the date? Thanks so much.


--
buzzharley
------------------------------------------------------------------------
buzzharley's Profile: http://www.excelforum.com/member.php...o&userid=35886
View this thread: http://www.excelforum.com/showthread...hreadid=559474

Thanks so much for your help Jim- Where should I put it in the macro? I
assume it would be in the first macro?


Workbook_Open()
If DatePart("m", Date) = "1" Then
Sheets("Jan").select ' or however you have named the January sheet
elseif DatePart("m",Date) = "2" then
Sheets("Feb").select
'through the twelve months

Dim dTime As Date
dTime = Time
If dTime = TimeValue("9:30 PM") And _
dTime < TimeValue("9:40 PM") Then
ImportData
End If
End Sub


--
buzzharley
------------------------------------------------------------------------
buzzharley's Profile: http://www.excelforum.com/member.php...o&userid=35886
View this thread: http://www.excelforum.com/showthread...hreadid=559474

Is "ImportData" the name of the second macro? If so, I would place it there
at the first. After the macro has determined and selected the proper
worksheet, it can then locate the correct cell(s) for inserting the new data.

I am heading home from work but will check back as soon as I can so I can be
sure I am really helping.

Jim


"buzzharley" wrote:


Thanks so much for your help Jim- Where should I put it in the macro? I
assume it would be in the first macro?


Workbook_Open()
If DatePart("m", Date) = "1" Then
Sheets("Jan").select ' or however you have named the January sheet
elseif DatePart("m",Date) = "2" then
Sheets("Feb").select
'through the twelve months

Dim dTime As Date
dTime = Time
If dTime = TimeValue("9:30 PM") And _
dTime < TimeValue("9:40 PM") Then
ImportData
End If
End Sub


--
buzzharley
------------------------------------------------------------------------
buzzharley's Profile: http://www.excelforum.com/member.php...o&userid=35886
View this thread: http://www.excelforum.com/showthread...hreadid=559474

Yes the second macro is the import data macro, the first being th
button macro. I just realized that I will have to mess with it ever
month anyways due to task manager only opening up the one mont
workbook unless I tell it to open all of the workbooks

--
buzzharle
-----------------------------------------------------------------------
buzzharley's Profile: http://www.excelforum.com/member.php...fo&userid=3588
View this thread: http://www.excelforum.com/showthread.php?threadid=55947

Buzz,

I have realized that I gave the wrong code sequence for what you need. Let
me be sure I understand now. You have a separate workbook for each month and
each workbook has separate sheets for each reporting date?

If this is the case then the ImportData macro would start like this:

If DatePart("m", Date) = "1" Then
Workbooks("Jan.xls").open ' or however you have named the January workbook
elseif DatePart("m",Date) = "2" then
Workbooks("Feb.xls").open
'through the twelve months
end if

Depending on the source of the data, you might be able to reference the date
on the document for determining the sheet to select. If you will give me
some more exact info about that part I will try to help work that out.

Jim


"buzzharley" wrote:


Yes the second macro is the import data macro, the first being the
button macro. I just realized that I will have to mess with it every
month anyways due to task manager only opening up the one month
workbook unless I tell it to open all of the workbooks?


--
buzzharley
------------------------------------------------------------------------
buzzharley's Profile: http://www.excelforum.com/member.php...o&userid=35886
View this thread: http://www.excelforum.com/showthread...hreadid=559474