Need to copy specific cells into an existing worksheet based on date entered by user

**jrfleury** · 10-12-2011, 09:28 AM

Hi all,
I am looking to gather data from Sheet (“NSF Log”) and copy it to Sheet (“Journal”) based on the date found in column “B” of Sheet (“NSF Log”). The intent is that using a command button, a message box will appear and the user can enter the date. The system will then look through column B of Sheet (“NSF Log”) to find the date and whenever there is a match, it will copy specific information on the corresponding rows as follows:

"NSF Log” copy columns “O” to “V” and paste it to “Journal” starting on “C17”.
“NSF Log” copy column “K” and paste it to “Journal” starting on “K17”.
“NSF Log” copy column “A” and paste it to “Journal” starting on “M17”.

Also, before copying the data to Sheet "Journal", I would like the system to erase any existing data from prior searches.

I have included a copy of the excel spreadsheet with sample data.

I don't know much about VBA, but any help is greatly appreciated!

**tigeravatar** · 10-12-2011, 10:59 AM

jrfleury,

Something like this?

Please Login or Register  to view this content.

**jrfleury** · 10-12-2011, 11:22 AM

Thanks for the quick response tigeravatar.

Unfortunately it's not working 100%. When I run the code, the message box to enter the date pops up, but when I enter a date, ie 18-Sep-11, I get the message that it is an invalid date. I've tested it with other dates too, but the same message appears.

I don't understand VBA code, so I'm not sure how to fix this.

Any ideas?

**tigeravatar** · 10-12-2011, 11:29 AM

jrfleury,

I think I missed keyword Set for rngResults, give this a try:

Please Login or Register  to view this content.

**jrfleury** · 10-12-2011, 11:43 AM

That works perfectly!

One last thing tho...when i lookup a date, say 18-Sep-11, the data copies over exactly as needed

but when I do another search, say 21-Sep-11, I get an Run-time error 1004. I'm guessing this is because there is currently information in the cells in sheet "Journal". Is there a way to clear the existing data in the sheet "Journal" before the information is copied from sheet "NSF Log"?

I truly appreciate all of your help!

**tigeravatar** · 10-12-2011, 12:34 PM

jrfleury,

Updated code:

Please Login or Register  to view this content.

**jrfleury** · 10-17-2011, 09:44 AM

This works perfectly!!

I wish I had a better understanding of VBA language so I could help other users as well.

Thank you so much for your help. Much appreciated!

Need to copy specific cells into an existing worksheet based on date entered by user

LinkBack

Thread Tools

Rate This Thread

Display

Need to copy specific cells into an existing worksheet based on date entered by user

Re: Need to copy specific cells into an existing worksheet based on date entered by u

Re: Need to copy specific cells into an existing worksheet based on date entered by u

Re: Need to copy specific cells into an existing worksheet based on date entered by u

Re: Need to copy specific cells into an existing worksheet based on date entered by u

Re: Need to copy specific cells into an existing worksheet based on date entered by u

Re: Need to copy specific cells into an existing worksheet based on date entered by u

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions