Macro to automate monthly spreadsheet pulling dates and info from Sheet1 to Sheet 2

Hi All,

I am trying to use a date on one sheet to auto populate cells in another worksheet. I have set up the date to auto populate, but what i want to do is run a monthly spreadsheet, and it to pull the info from Sheet1` and put in to a table in Sheet 2 next to the corresponding date. So say in sheet one is have the date cell,(auto populates) and then a few cells with sales purchases total etc. I then want the info from thos cells to be put into the monthly table in the next worksheet. Any ideas?

Hi Please see attache work book.
Any help would be much appreciated.
Cash Count Master Test.xlsx

Hi,

Thing is, i know what i want to happen, i just dont quite know how to explain it so i will try again.

I want to remove all but 1 of the 31 duplicate sheets (we will call this INPUT SHEET) in the work book. The figures in the table in the MONTH STATS Sheet are found from the INPUT SHEET (done with formatting at the moment)

The formula i can think to describe what i want to happen is as follows (however i cant get it to work, so though it might have to be a macro.):

=IF INPUT SHEET 'B2' = MONTH STATS 'A2' then MONTH STATS ('C2' = INPUT SHEET 'D26') and ('D2' = INPUT SHEET 'D27') ETC ETC.
(DATE) (DATE) (CASH) (CASH) (W&P) (W&P)

Then i will need to do the reverse to auto populate cells in the INPUT sheet from the previous date:
= IF INPUT SHEET 'B2' = MONTH STATS 'A3' then INPUT SHEET 'D30' = MONTH STATS 'F2' ETC ETC
(DATE) (DATE) (OPENING BALANCE) (CLOSING BALANCE(PREVIOUS DATE)
I will also need to come up with and use an CLEAR contents macro on the INPUT SHEET to clear the cells that are inputed manually.

Hope this makes sense, and thanks very much for your time.

This is what i can make of it


Kind regards
Leo

Thanks Leo, It is pretty much what i was after.

Next thing, i am making some adjustments (as you do) can you explain the code used for moving the figures from the Input sheet to the Moth Stat Sheet? I am putting in a more in depth month reconciliation. I will attach a worksheet, but what i want to do is pick what cell i want to get the info from and pick where it ends up. Much like you have done at the bottem of the code.
Cash Count Master Test.xlsx

Again, Any help is appreciated.

Hello,

sorry I don't understand the question, or what you want to achieve.


Kind regards
Leo

Attached the wrong File....

Cash Count Master L B.xlsm

On the new Sheet AUG 2015, I am wanting to fill all columns from a selection of cells on the input sheet (which haven't all put put on there yet). On your code I think it takes the selected cells and puts those figures in a table (1, 7) in the Month Stat Sheet. I can do this for the new sheet AUG 2015, but i am using around 45 columns (to account for the different Denominations for Driver 1, 2, 3, 4. But because some columns in AUG 2015 are going to be formatted from cells in the same sheet (eg. columns B,E,I,L,O ETC) other cells (eg. C,D,F,G,H,K,M,N,R-AS) will need to get their figures from the input sheet. (as you have done for B2, B15, D26, D27, D28, D29, D31 etc)

So wondering if there is an easier way to hand pick the cells form INPUT, and assign them a cell in AUG 2015.

The next thing will be to take the cells in AUG 2015 (r-as etc) will need to put on the next days INPUT sheet. So the denominations out for driver 1 on say, the 20th will need to put into the denominations in for driver 1 on the 21st and so on.

I am pretty sure given your code i could figure this out (after a lot of time, trial and error) so if you can help out in any way it would be great. If if you could explain the code so i can make the changes myself
IE: Range("B16") = Sheets("Month Stats").Range("C" & r)
Where B16 on INPUT Sheet will = Column C on AUG 2015 Sheet using the date as a reference.

Ok lets see, i only put some explication in the code
maybe you can manage, if not give a sign.


Kind regards
Leo

That makes sense. But how would you skip a column in the AUG 2015 sheet? So you have the 7 elements but say column "D" is to be formatted by the figure in "A" and "B", how would you write the code to make sure nothing gets put into column "D", but still in the remaining 3 columns?

this is one way: on the place you put "" in the array like

Please Login or Register  to view this content.

but then you lose the formula or wathever is in column D

so when you dont wanne lose any data or formula, you need
a second or even third array.
In this exemple, you resize the E column for the second array

Kind regards
Leo

also possible,

=array(element1,element2,element3,element1+element2,element4)

Hi Again,

So when I push on the "PROCESS" button, it transfers the info across to the SEP 2015 page, but it somehow deletes a couple of rows. So if you were to manually put the date in as the 1/09/2015, it deletes a couple of rows above it and does some weird things. Any idea on what's going on here?

Cash Count Master L B2 (1).xlsm

have to change this

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

because in this exemple the first of the month is at row 4

or you can use the match function to search the rownumber

Kind regards
Leo

Thanks that has worked. The workbook is working exactly how i want it to now thanks to you!!!!

Now, the last things i want to sort...

I have now set up (and will for next year as well) a page for every month of a particular year. On the 1st of January each year i will manually transpose the information from the previous day, but as for the rest of the month sheets, how do i write the code to
A) Change what page the info will be moved to based on the new months date;
B) Get the previous days figures (end of month) on to the 1st day of the new month.

What i also want to do is format the different boxes on the input sheet to appear after selected cells have a figure in them, to sort of have a step by step input method. So for instance cells F3 - M16 will be blanked out untill a figure has been put into all cells C5 - C14, than cells O2 - Q26 will appear when celss F3 - M16 have something in them. etc etc.

for the first part of the question

cange the first line in code to this

Please Login or Register  to view this content.

and all the places in code where is use of sheetsnames by month and year like("aug 2015"), replace by sheets(ShNa)
the values will come in the right sheet, if the sheet exists in the workbook.

Kind regards
Leo

Have placed this code in but still wont work. Its bringing up error message Syntax Error on the code
ShNa = Format(dat, "mmm") & " " & Format(dat, "yyyy")

it only work if sheet names are like sep 2015 or aug 2015 and the date in sheet input is in range B2

tested in this exemple



Kind regards
Leo