[VBA request] Select first entry of todays date + last row of a column and copy/paste?

Hey team! Sorry for the confusing title, my tiny brain cant figure out how to word it. Thank you for reading as well! I see a ton of activity here which is insane. I dont know how some of you guys rip through answers so quickly all day :D :D

TLDR: I have varying dates in column A and need to identify the first entry with todays date and select everything else to the bottom-right/end of the table. Is this possible in VBA?

Anyway so what I have is a table that auto fills based on user emails and its listed by date. The latest entry auto adds to the table as a new row in it. At the end of the day I copy all of this info into a cell of a different sheet which has some VBA and formulas to auto pull info from it to create a presentable email of stats for the day. I want to basically make it so literally all I have to do is download a local copy of the Excel web file, click a button and it does the rest. So I just have to automate the last bit.

Is it possible to make a VBA script that identifies the first column A date cell based on today, then copy everything from that cell in column A, to the very last existing row in, say, column C or whatever. The nature of these entries being done by users means that sometimes when users forget to submit their entries, they wait for the next day and mark the "current date" as yesterday, so technically I cannot rely on each row having todays date exclusively. I've seen up to 5 days old so this is why I was thinking it would be awesome if you could just grab the first cell of todays date, and finish up the range, copy, and paste in a different sheet.

The pasting and switching to sheet bit I can do, I just dont know how I could grab the range based on this issue. Does that make sense?

Here is a screenshot (just a dummy)

Screen Shot 2021-12-28 at 1.14.22 PM.png

Edit: Issue is solved

My end result is this code, which from a different sheet selects all cells from the first entry containing todays date, down to the last row of a specified amount of columns, copies the info, and pastes into a cell from another different sheet

Please Login or Register  to view this content.

crossposted: https://www.mrexcel.com/board/thread...olumn.1191333/

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved 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.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future

I thought I knew what I was doing but I guess not

I've exclusively been experimenting in that file you made, baby steps you know!

I thought your code would visually select the relative cells and then I would just add the "Selection.Copy" and Range("xxx").Select
ActiveSheet.Paste

It doesnt work

I intend on leaving the original selection there, just copying the info and pasting in a cell (and yes it will be in a different sheet)

Thank you very much for helping me btw!!

Originally Posted by jindon

Try

Please Login or Register  to view this content.

Edit: Woah it actually worked when I tried just now on the next day. I didnt even change anything. Maybe it was something unrelated and the shutdown / restart fixed the issue? My god I have no clue what just happened and why it works now lol

It doesnt work with a dev button but if I make my own via shape, it works

Originally Posted by jindon

Change Sheet1 to Sheet 1

Damn, same error on the same line.

I removed all modules, force quit Excel, reopened the macro enabled workbook, created a button > assign macro > new > replaced everything with your code, added a space and saved/closed VBA, then clicked the button and I get error 9. Debug shows the With Sheets line as yellow

Edit: I just had to use the actual sheet name, "Tracker" Good to go!

Originally Posted by jindon

Change Sheet1 to Sheet 1

You are freakin awesome.

I DM'd you asking a question, before you go ahead and answer this. The initial thread question has been solved!!

-

Ok everything is working in regards to selecting what I need and pasting where I need it to be pasted, but for some reason if I manually paste this info in the target table, the other table which references each row, cell by cell does fill properly, but the next table that searches a word based on the reference table info shows #value!. Does that make sense? and do you know what could possibly cause this?

Its a bit hard to explain so here is a screenshot of what my actual table is:

Empty table - This is without anything entered in B4 (where the table starts):
Attachment 761336

Manually pasted info into B4:
Attachment 761337

Auto filled based on your code, automatically entered into B4:
Screen-Shot-2021-12-29-at-8-57-26-PM.png

Originally Posted by jindon

Your attachments are all invalid.

Upload a workbook that you are working with.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hey! I just realized that the script does everything I need unless something in the table has an # error in it. Is this fixable?

Edit: Nvm this issue went away when I set a column count