copy info to form based on date

[jimb0693]

hope that makes sense, what im trying to do is copy info from sheet 1 to a form on sheet 2, print it off. then clear the info in the form and start again on the next line.

just to make it interesting i want it to select the info from, say cells a1 and a2 based on a date in cell a3 if it falls within the last month.

it would take the info from a1 & a2 and paste it into cells a2 and a5 on sheet 2, send the entire sheet 2 to the printer, clear cells a2 and a5 then look back at sheet 1 for the next matching date

not sure if i'm explaining this very well, its been doing my head in for about a month now and i'm no nearer a solution.

any help, code, or even pointers would be greatly appreciated

[Palmetto]

It will be best if you upload a sample workbook that shows your data structure and the results you want to achieve.

If sheet1 contains a table of data from which a look up can be performed, then formulas can be used on sheet2 to return corresponding matching values.

See attached example of basic idea.

[jimb0693]

if you look at this sheet 2 now contains info on employee no 5 because he has completed 5 jobs and the last one was completed in the last month, what i would like to have the workbook do is update the info on sheet 2 automatically print the page, then clear it and look for the next match

[jimb0693]

hi, can anyone help on this or will what i'm trying to do never work?

[Palmetto]

In the attached . . .

On the report sheet:
Removed Merged Cells and used Center Across Selection. Merged cells should be avoided.
Manually resized rows to give same appearance as merged cells

On the Data sheet
Modified the date entry (employee 8, job 4)to test looping through the cells

Here is the code, which you can assign to a button and run at will.
Note: I have set this up to PrintPreview so you can test that it works as you want. If satisfied with the results then change "PrintPreview" to "PrintOut as noted in the code.

This code compares the month in column-M dates to the current month and loops through the cells top copy and print for each match.

Please Login or Register  to view this content.

[jimb0693]

this seems to be almost what i need, it runs through and picks up all the dates in the current month which is fine, but it doesnt pick up the names from column A, i cant see why this is not happening, if im reading it right the line

Sheet1.Range("B15") = c.Offset(-13, 0).Value

should be getting the names but its just leaving a blank space on the report, i've tried changing the offset value but with no effect.

any suggestions, or am i looking at the wrong line entirely?

[Palmetto]

Typo error. The offset value should read

Please Login or Register  to view this content.

BTW, when posting ANY code, per forum rules, be sure to wrap the code using cade tags. This is easily done by selecting all of the code then clicking on the # button.

[jimb0693]

works perfectly now, i didnt think of reversing the offset, thanks for all your help,

also i did know about wrapping code, i just genuinely forgot to do it this time

thanks again