Finding Date(s) in a Column in Order to Label Report as Either Single Date or Date Range

[FrenchCelt]

Hello,

I have a report that would either cover a single day, a week, or a 4-week range. The date(s) are displayed in Column D of my report and what I want to do is to label the cell in F2 the single date or have the date range in F2 and G2 depending on what is found in Column D in a macro.

So for example, if it only finds one date in Column D, I would like that date to be copied into F2.

If it finds a range of seven days, I would like cell L1 to say Weekly Productivity and the earliest date to be copied into F2 and the latest date into G2.

If it finds a range of 28 days, I would like cell L1 to say 4-Week Rolling Report and the earliest date to be copied into F2 and the latest date into G2.

Any help would be appreciated.

 

[FrenchCelt]

I've had other macros that change the sheet name and then refer to that sheet name later on in the code with no issues. And Export is the name of the sheet upon opening the file after exporting from Power BI to Excel. I checked for spaces before and after the text.

 

[BigBeachBananas]

You've probably used the CODENAME of the sheet instead of the visible sheet name.

Try

Set ws = Sheet1 ' Change to your actual code name

 

[BigBeachBananas]

Also, make sure you place the code in the right workbook (if you have multiple open).

 

[FrenchCelt]

You've probably used the CODENAME of the sheet instead of the visible sheet name.

Try

Set ws = Sheet1 ' Change to your actual code name

The code wouldn't execute using that (I changed Export to Report on the worksheet and Sheet1 to Report in the code), but I tried this and didn't get an error message:

Set ws = Sheets("Report")

However, nothing happened after I ran the rest.

 

[BigBeachBananas]

The code wouldn't execute using that (I changed Export to Report on the worksheet and Sheet1 to Report in the code), but I tried this and didn't get an error message:

Set ws = Sheets("Report")

However, nothing happened after I ran the rest.

Can you share your workbook via DropBox (without any sensitive info)?

 

[FrenchCelt]

I'll find out from our IT dept.

 

[BigBeachBananas]

I'll find out from our IT dept.

Try to run the code in a fresh workbook. Put some random dates in column D, the macro should work. Not sure why your workbook isn't picking up on the sheet name.