VBA help please

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
Hello All,

As you all know by now that I am a person with very limited knowledge of VBA. Still I try to make VBAs work for me. Now I have another question for you gurus. I have the following macro. It runs alright if the dates in seven tabs are within the same month. As soon as the dates cross over to next month in any tab, the macro asks for the file to be located. Is this normal to happen? If not, how can I avoid the dialogue box to come up and confuse the users., Also, I will appreciate anyone making this macro smaller and neater.

Code:
Sub NewGAP()
pw = InputBox("Please enter password to run this macro")
If pw = "bus" Then
Dim rng As Range, d As Date, rngB As Range, fd As Date
Dim fp1 As String, fp2 As String, fp3 As String, fp4 As String, fp5 As String, fp6 As String, fp7 As String
Dim c As Range
Sheets("Sunday").Activate
    With ActiveSheet
    d = InputBox("Please enter date for which you want the GAP file.")
    Range("E1").Value = d
    fp1 = "'S:\Rosters\[FY17 Gap projections - " & Range("B4").Value & ".xlsx]" & Range("A4").Value & "'!"
    Set rng = .Range("A6", "A30")
    rng.Formula = "=Index(" & fp1 & "$B$1:$T$1000,Match($C$4," & fp1 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:A6)+8)"
    Set rngB = .Range("B6", "D30")
    Set rngC = .Range("G6", "I30")
    rngB.Formula = "=Round(Index(" & fp1 & "$B$1:$T$1000,Match($C$4," & fp1 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:B6)+8),0)"
    rngC.Formula = "=Round(Index(" & fp1 & "$B$1:$T$1000,Match($C$4," & fp1 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:G6)+8),0)"
    End With
Sheets("Monday").Activate
    With ActiveSheet
    fp2 = "'[FY17 Gap projections - " & Range("B4").Value & ".xlsx]" & Range("A4").Value & "'!"
    Set rng = .Range("A6", "A30")
    rng.Formula = "=Index(" & fp2 & "$B$1:$T$1000,Match($C$4," & fp2 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:A6)+8)"
    Set rngB = .Range("B6", "D30")
    Set rngC = .Range("G6", "I30")
    rngB.Formula = "=Round(Index(" & fp2 & "$B$1:$T$1000,Match($C$4," & fp2 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:B6)+8),0)"
    rngC.Formula = "=Round(Index(" & fp2 & "$B$1:$T$1000,Match($C$4," & fp2 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:G6)+8),0)"
    End With
Sheets("Tuesday").Activate
    With ActiveSheet
    fp3 = "'[FY17 Gap projections - " & Range("B4").Value & ".xlsx]" & Range("A4").Value & "'!"
    Set rng = .Range("A6", "A30")
    rng.Formula = "=Index(" & fp3 & "$B$1:$T$1000,Match($C$4," & fp3 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:A6)+8)"
    Set rngB = .Range("B6", "D30")
    Set rngC = .Range("G6", "I30")
    rngB.Formula = "=Round(Index(" & fp3 & "$B$1:$T$1000,Match($C$4," & fp3 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:B6)+8),0)"
    rngC.Formula = "=Round(Index(" & fp3 & "$B$1:$T$1000,Match($C$4," & fp3 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:G6)+8),0)"
    End With
Sheets("Wednesday").Activate
    With ActiveSheet
    fp4 = "'[FY17 Gap projections - " & Range("B4").Value & ".xlsx]" & Range("A4").Value & "'!"
    Set rng = .Range("A6", "A30")
    rng.Formula = "=Index(" & fp4 & "$B$1:$T$1000,Match($C$4," & fp4 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:A6)+8)"
    Set rngB = .Range("B6", "D30")
    Set rngC = .Range("G6", "I30")
    rngB.Formula = "=Round(Index(" & fp4 & "$B$1:$T$1000,Match($C$4," & fp4 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:B6)+8),0)"
    rngC.Formula = "=Round(Index(" & fp4 & "$B$1:$T$1000,Match($C$4," & fp4 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:G6)+8),0)"
    End With
Sheets("Thursday").Activate
    With ActiveSheet
    fp5 = "'[FY17 Gap projections - " & Range("B4").Value & ".xlsx]" & Range("A4").Value & "'!"
    Set rng = .Range("A6", "A30")
    rng.Formula = "=Index(" & fp5 & "$B$1:$T$1000,Match($C$4," & fp5 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:A6)+8)"
    Set rngB = .Range("B6", "D30")
    Set rngC = .Range("G6", "I30")
    rngB.Formula = "=Round(Index(" & fp5 & "$B$1:$T$1000,Match($C$4," & fp5 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:B6)+8),0)"
    rngC.Formula = "=Round(Index(" & fp5 & "$B$1:$T$1000,Match($C$4," & fp5 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:G6)+8),0)"
    End With
Sheets("Friday").Activate
    With ActiveSheet
    fp6 = "'[FY17 Gap projections - " & Range("B4").Value & ".xlsx]" & Range("A4").Value & "'!"
    Set rng = .Range("A6", "A30")
    rng.Formula = "=Index(" & fp6 & "$B$1:$T$1000,Match($C$4," & fp6 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:A6)+8)"
    Set rngB = .Range("B6", "D30")
    Set rngC = .Range("G6", "I30")
    rngB.Formula = "=Round(Index(" & fp6 & "$B$1:$T$1000,Match($C$4," & fp6 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:B6)+8),0)"
    rngC.Formula = "=Round(Index(" & fp6 & "$B$1:$T$1000,Match($C$4," & fp6 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:G6)+8),0)"
    End With
Sheets("Saturday").Activate
    With ActiveSheet
    fp7 = "'[FY17 Gap projections - " & Range("B4").Value & ".xlsx]" & Range("A4").Value & "'!"
    Set rng = .Range("A6", "A30")
    rng.Formula = "=Index(" & fp7 & "$B$1:$T$1000,Match($C$4," & fp7 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:A6)+8)"
    Set rngB = .Range("B6", "D30")
    Set rngC = .Range("G6", "I30")
    rngB.Formula = "=Round(Index(" & fp7 & "$B$1:$T$1000,Match($C$4," & fp7 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:B6)+8),0)"
    rngC.Formula = "=Round(Index(" & fp7 & "$B$1:$T$1000,Match($C$4," & fp7 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:G6)+8),0)"
    End With
Else:
Call MsgBox("You do not have the permission to run this macro.")
Exit Sub
End If
fd = d + 6
ActiveWorkbook.SaveAs Filename:="S:\Planning and scheduling\Rosters\Bus Check\Bus Capacity Check - Week Ending " & Format(fd, "dd mmmm yyyy") & ".xlsm"
End Sub

Thanks a lot
Regards
Asad
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Can you point out where the "dates in the tabs" are present in your code? Also, which line in your code causes the dialog box to appear when there's a date crossover?
 
Last edited:

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
Hello Joe,

The dates are in E1 of all the sheets. Only in Sunday Tab E1 gets populated by input from user with this:
Code:
d = InputBox("Please enter date for which you want the GAP file.")
    Range("E1").Value = d

Rest of the sheets have a simple formula, like Monday E1 has
Code:
=Sunday!E1+1
and so on.

This worked perfect when the user generated the files for January 2017. Until he reached the week starting 29th January. Wednesday being the 1st of February, thta's where he got the message to select the file. Originally, it was asking the same question thousand times, but then I inserted filepath for each sheet. Now it is asking only oince. This is not bad. We can work with this. But, why is it asking. The filepath is basically picking up values from cells B4, and A4 for each tab and these cells are just formatted values from cell E1 (date for the sheet). B4 is formatted as "yyyy mm mmmm", and A4 is formatted as "mmmm yy". The reason is that's how the source files have been saved with those names.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hello Joe,

The dates are in E1 of all the sheets. Only in Sunday Tab E1 gets populated by input from user with this:
Code:
d = InputBox("Please enter date for which you want the GAP file.")
    Range("E1").Value = d

Rest of the sheets have a simple formula, like Monday E1 has
Code:
=Sunday!E1+1
and so on.

This worked perfect when the user generated the files for January 2017. Until he reached the week starting 29th January. Wednesday being the 1st of February, thta's where he got the message to select the file. Originally, it was asking the same question thousand times, but then I inserted filepath for each sheet. Now it is asking only oince. This is not bad. We can work with this. But, why is it asking. The filepath is basically picking up values from cells B4, and A4 for each tab and these cells are just formatted values from cell E1 (date for the sheet). B4 is formatted as "yyyy mm mmmm", and A4 is formatted as "mmmm yy". The reason is that's how the source files have been saved with those names.
You didn't answer this question: Also, which line in your code causes the dialog box to appear when there's a date crossover?

I'm guessing that when you crossover to a new month the Filepath you are building (e.g. fp1, fp2 , ...) in your formulas doesn't exist. The dialog box wants you to identify the missing file. Hard to diagnose w/o seeing what's in your sheets and understanding what exactly you want to achieve.
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428

ADVERTISEMENT

You didn't answer this question: Also, which line in your code causes the dialog box to appear when there's a date crossover?

I'm guessing that when you crossover to a new month the Filepath you are building (e.g. fp1, fp2 , ...) in your formulas doesn't exist. The dialog box wants you to identify the missing file. Hard to diagnose w/o seeing what's in your sheets and understanding what exactly you want to achieve.

Sorry about that, but all I know is that we get message to select the file for data for new month and my guess is in this case, it is happening on Wednesday sheet. So the line would be
Code:
Sheets("Wednesday").Activate
    With ActiveSheet
    fp4 = "'[FY17 Gap projections - " & Range("B4").Value & ".xlsx]" & Range("A4").Value & "'!"
    Set rng = .Range("A6", "A30")
    rn[COLOR=#ff0000]g.Formula = "=Index(" & fp4 & "$B$1:$T$1000,Match($C$4," & fp4 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:A6)+8)"[/COLOR]
    Set rngB = .Range("B6", "D30")
    Set rngC = .Range("G6", "I30")
    rngB.Formula = "=Round(Index(" & fp4 & "$B$1:$T$1000,Match($C$4," & fp4 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:B6)+8),0)"
    rngC.Formula = "=Round(Index(" & fp4 & "$B$1:$T$1000,Match($C$4," & fp4 & "$B$1:$B$1000,0)+ Rows(A$6:A6)-1, Columns($A6:G6)+8),0)"
    End With
I am not sure how to find out which line in code is causing the message to pop up.
 
Last edited:

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
All I am trying to do is copy the data from files that belong to the relevant month, and paste it in the current file. We do this for comparing last year's data to this year's data.
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,428
Finally worked it out :)
Thanks a lot for trying Joe.
The mistake was that in my filepath, I had not defined the full filepath by including the drives and parent folder names. As soon as I included those, the code worked perfect.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,910
Messages
5,598,813
Members
414,260
Latest member
joishe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top