Copying cells from a frozen pane

Hi All,

I'm creating a new planner for the new year and im putting all the dates increasing in the columns so that as we progress through the year we will have to scroll right for the further through the year we go. ie. Cell B1 is Jan 1st, Cell C1 is Jan 2nd and D1 is Jan 3rd.

This way of doing it makes it a lot easier for me to view individual days than ways we have usually done our work planner.

this however is gonna cause me issues as I don't want to have to have to spend 30 seconds scrolling to the right everytime I want to get to todays date especially further through the year.

Is there a way that I can create a formula or vba code that will make todays date the leftmost column when you open the document? ie. Currently the 4th of March is Cell ref AU1 - I would want this cell to be the leftmost column on that date.

To make this more difficult I have all column A which has our employees running down and this column is a frozen pane so that as you scroll through the calendar you can always see the employee on the left hand side, so I guess, todays date would want to be in the 2nd most left column after Column A


The second issue I think I'll have is that if I want to copy some information for a particular week and I want to show the employee and their weeks calendar I click row A and then drag it across to select that week which for example might be columns G-K it wont let me just do this and it naturally springs back so that the copy would go from cells A-K rather than just A & G-K which I would want. This isn't too bad in the 1st weeks in Jan, but when I get part through the year, this will get really annoying! - If I try and just click cell A (whilst holding shift) and then cliking and dragging G-K it says this wont work on multiple selections when I try and copy the data.


Is any of the above possible or am i wasting my time and do i need to look into a different format for the calendar? - Really want to avoid having different tabs/worksheets per working week as i'm wanting to use the excel table data in powerapps so i'm ideally wanting only one table for the entire year


I have attached a very basic calendar with my personal data removed.

Thanks

Try this:

Please Login or Register  to view this content.

End Sub

Sorry, should have noted that this assumes column A is static (i.e., you have Freeze Panes applied).

You need to insert the code into the worksheet code page and name it Private Sub Worksheet_Activate() instead of Sub ScrollDate().

Thank you for your reply! I will try this when I’m back in the office on Monday, will this also mean that I can copy across the the cells by clicking the frozen pane (A) and dragging to just column G-K or is your code primarily so that the correct date is always on the left?

Thanks again

The code merely scrolls the window so the current date is now adjacent to column A (assuming Column A is locked by Freeze Panes). If you want to drag column A across several columns, that will work, but it will drag across columns B, C, D, etc, not starting with the column for today's date which just happens to be scrolled next to Col A. For instance if A2 contains "Test", when you activate the worksheet on Jan 10, Column K will be scrolled next to Column A. If you now select cell A2 and drag it to the right, the cells to the right will also contain "Test" until you stop. The window unscrolls to match the cursor position. If you stop at Jan 10, then all the the cells B2:B10 will contain "Test". This is just normal Excel behavior.

If you want a means to fill in the items from Col A into the column for the current date, that wouldn't be hard to implement. From your first post, it sounds like you want some other data from elsewhere in the calendar copied to the current date column. If that's the case, it may be more challenging, at least for me. Is the data just text, or is there formatting as well? Would the data remain in the same row as the previous data, or could it be anywhere? If you can be a bit more descriptive it might help.

If you want to copy data from elsewhere in the sheet, I might suggest an alternate method: Select View/New Window, and use the second window to scroll to where your source data is, copy it , and then select target cell in the original window and paste it there. I find that scrolling around in a single window between two disparate sections of a worksheet makes me crazy, so I often use this as a quick workaround.

Looking at this a bit more, I think you need both a Worksheet_Activate event macro as noted above and a Workbook_Activate macro to handle the condition when the workbook first opens. I have added these to your workbook, attached, plus I added an extra window as noted above.

Hi, thanks again, unfortunately cannot open the document just yet till I get to the office on Monday, but looking forward to seeing what you have done as it sounds like it’s going to work brilliant.

Just to help you understand a bit better, the reason why I’m wanting to select cells like Column A and then potentially for example columns S-W would be so that I can copy these cells ( so ideally 6 rows, not 23!) and then paste them into an email to send the employee to show what they have booked in for the week. I would initially filter the table so that only that employee is showing in Column A I would than select the rows with the header and the row for that employee and then just that working week. So that when I paste in the email you would have 6 columns. (Their name and then Monday-Friday)

Does that make more sense?

Thanks again.

Hi there, managed to open the document and have a look how it works, seems to sort of work when opened up from a desktop, however the date it jumps to seems to be tomorrow rather than today. Not sure if this is because of the frozen pane?


The main issue seems to be that when I open online from the SharePoint environment it doesn't seem to work? - We generally use the online version of excel as 2-3 people might have this open at the same time and we historically had lots of issues with conflicting copies. - As this uses Vba and Macros, will this not work on excel online?


Also, wondered if there was anything as described above in order to copy only the frozen pane and then the current week.


Thank you

d3ell:

Sorry for the late response, I got the flu and have been out a few days.

I failed to notice that you did not have weekends included in your list of dates. to fix this, in the ScrollDates macro (in Module 1), replace the days = line with the following:

days = WorksheetFunction.NetworkDays(DateValue("1/1/" & Year(Now)), Now)

That will account for the weekends. If you are using other holidays, the networkdays function can account for those as well, but requires a list of holidays as part of the arguments.

Hope that helps.