How to advance to a Next day in a daily style report

bill1967

New Member
Joined
Mar 24, 2015
Messages
13
I have a need for searching through worksheets. I am working with Excel 2013. In a past job I had a file that had numerous dated tabs (worksheets) ranging in dates for the full year. I had a script that would each morning would kick off and copy data from a different file and then search through the sheets until it found the next sheet where a specific cell location was empty. It would then paste the copied data into that sheet which would then added data to that cell that was empty. The next day when I ran the script it would then progress to the next sheet in the series. I am now in a new job and have a file that I would like to repeat this process but cannot find a VBA code on the web that does what I had set up previously. I am thinking it was some variation of either a loop or an offset between the sheets. If I could get this process to work I could automate my process reducing the manual effort each day. Would anyone be able to provide any insights. I am an active user of VBA code but do not have the skills to create from scratch. Any help would be much appreciated.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
copy data from a different file - do you mean from a particular sheet in a different workbook ?
 
Upvote 0
What I have is, there is a set file "Yearly" and then I have exports from other systems and that are exported with defined names each day. I have copy the pieces of data from the exports and paste into the new day in the "Yearly" file. Currently I have to go into the "Yearly" file first to click on the appropriate tab before launching my macro that does the copying and pasting. In my last job I had a similar process and there was a piece of code I found that go into that "Yearly" file search through each tab until it found say cell D4 empty then move into it's copy/paste process. then as part of the process something would be pasted into that cell D4 so the next day when it did it's search it would then move to the next day in the file. Hopefully that makes sense.
 
Upvote 0
so a workbook may have 100 sheets and you want to check each one in turn for say cell D4 is empty, and if it is copy say G3:K9 into your YEARLY file ?
 
Upvote 0
Yes. That is very close to it. Having to manually place the cursor each day is the only thing preventing me from making this a fully automated process. I have been searching online for a couple months with no luck. I believe one of the programmers at my last job helped me with the one I had there. Basically the cell D4 is the cell the date would be pasted into so in the tabs for future days that cell would always be empty.
 
Upvote 0
if you write a macro to go to sheet 1 and check if cells(4,4) - that is D4 - is NOT empty, and if not empty select G3:K9, copy, goto YEARLY sheet, paste

you could then copy this code and paste it 99 times onto the end of the macro and manually change sheet1 to sheet2, sheet1 to sheet3 etc etc

would take 30 minutes but when it is done it is done

to avoid fruitless che cking as soon as it finds an empty D4 - exit the macro

Aladin will probably come up with more concise code................
 
Upvote 0
Would a loop do the same thing? I think I understand how to do it, I just do not know how to write the code to make it do it.
 
Upvote 0
if you write a macro to check if cells(4,4) - that is D4 - is NOT empty, and if not empty select G3:K9, copy, goto YEARLY sheet, paste and return to current sheet, and attach this macro to a red rectangle on the sheet, clicking the rectangle then runs the macro. Hold down control key, select the macro, edit copy, go to sheet 2, paste, go to sheet3, paste etc etc
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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
Back
Top