Printing multiple rows and columns from range based on certain criteria into new tab in workbook

[abutterworth]

Hi all,

I'm currently using Office 2003 to create a rolling forecast that can automatically update and populate. Because of my lack of vba knowledge I'm currently stuck on how to do a few things. I searched online how to copy multiple rows, and with it only specific columns, but could not find something that suited my templates need. Because my template references Cognos TM1, and has specific formulas, I need to copy certain columns out of my data sheet in order for this to run smoothly. I could use some help!

Little bit of background:

I have 3 tabs currently - The actual names of these tabs in my spreadsheet are in the ()'s

• Tab A: Has the data (tab is named DBRW)
• Tab B: Where I want the data to print out (tab is named MRefo)
• Tab C: Has "template" (tab is named Template)

In Tab A, I have many different values that need to be printed, but they should only be printed if they fall between the date range.

• Date range: start yyyymm (E4), end yyyymm (E5)
• All of the data is found in the range (A11:I2026) (this will remain consistent- no data will be added)
• Column H has the yyyymm that is specific to that line, and needs to be tested against E4/E5 to make sure it falls on one of the yyyymm or in between the yyyymm's.

In Tab B, I have a field starting at (A2:Pxxxxx ---- ***where xxxxx symbolizes the amount of rows with data that is found within the date ranges in Tab A) with some information/columns that are already filled in (these columns are consistent throughout- and WILL NOT change ever).

In Tab C - the range is (A2:N2), Columns A, B, F, G, H, I, J, K, L, N all will stay consistent, and just need to be copied over for the same amount of rows that Tab A copies over. They will paste into Tab B in the same cells that they are in their own template. There is no data in the template in cells C, D, E, M, O, P - all of these cells will be automatically populated by the information from Tab A when data is copied over to B.

Moving on to the data I need copied over to Tab B, FROM TAB A, if the row's date meets the criteria of falling between the date range.

• Column F (Tab A) needs to be copied over to Column C (Tab B)
• Column G (Tab A) needs to be copied over to Column D (Tab B)
• Column E (Tab A) needs to be copied over to Column E (Tab B)
• Column C (Tab A) needs to be copied over to Column M (Tab B)
• Column A (Tab A) needs to be copied over to Column O (Tab B)
• Column I (Tab A) needs to be copied over to Column P (Tab B)

So to give a brief overview. What I need is:

• "Filter" rows in Tab A so that they meet the criteria of the date ranges
• Print these rows that meet criteria in Tab A into given columns in Tab B
• Consistent "template" data from Tab C, needs to be copied over to Tab B. (I'm not sure if this should be done before or after, in case it would overwrite data that Tab A transferred to Tab B.)

I would also like the macro to clear out all data on Tab B (except A1:P1) PRIOR to executing it. I plan on assigning this to a button, and my coworkers may not realize that there is prior data left on the sheet.
If you have any further questions please let me know. All help is greatly appreciated - I hope my explanation isn't too confusing.
Thanks!

As a heads up I posted this same question on stackoverflow and was voted down because I did not include my own code- the reason behind this is because I looked online for any help and did not find any information that (like I said) suited the template. If you want me to post what I have I can, but I can guarentee you that it will be a disaster and not benefit you whatsoever.
excel - Print dates between range - Stack Overflow


Thank you.

 

[sijpie]

Clearly explained. Will see what I can do. Not very difficult, just need to get it right.

 

[sijpie]

Are there any formulas in the template sheet, or are these values?

 

[sijpie]

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#007F00">'-----------------------------------------------------------</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> CopyInfoDBRW()<br><SPAN style="color:#007F00">'</SPAN><br><SPAN style="color:#007F00">' Macro to copy rowsn from DBRW sheet mixed with data from _<br>  Template sheet to  sheet MRFO if date is between values _<br>  in E4 and E5</SPAN><br><SPAN style="color:#007F00">'-----------------------------------------------------------</SPAN><br><br>    <SPAN style="color:#00007F">Dim</SPAN> rA <SPAN style="color:#00007F">As</SPAN> Range, rB <SPAN style="color:#00007F">As</SPAN> Range, rC <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> lA <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lB <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lEarly <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lLate <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> aOut <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, aIn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, aT <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rA = Sheets("DBRW").Range("A11:I2026")<br>    <SPAN style="color:#00007F">Set</SPAN> rC = Sheets("Template").Range("A2:N2")<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rB = Sheets("Mrefo").Range("A1")<br>    <SPAN style="color:#007F00">' delete any old entries</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> rB.CurrentRegion.Rows.Count > 1 <SPAN style="color:#00007F">Then</SPAN><br>        rB.CurrentRegion.Offset(1, 0).EntireRow.Delete<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#007F00">' copy data to arrays for fast processing</SPAN><br>    aIn = rA<br>    aT = rC<br>    <br>    lEarly = Sheets("DBRW").Range("E4")<br>    lLate = Sheets("DBRW").Range("E5")<br>    lB = 1<br>    <SPAN style="color:#007F00">' set size for output array to column A-P (16)</SPAN><br>    <SPAN style="color:#00007F">ReDim</SPAN> aOut(1 <SPAN style="color:#00007F">To</SPAN> 1, 1 <SPAN style="color:#00007F">To</SPAN> 16)<br>    <br>    <SPAN style="color:#007F00">'loop down the input array</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> lA = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(aIn, 1)<br>        <SPAN style="color:#007F00">'check date is between</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> aIn(lA, 8) >= lEarly And aIn(lA, 8) < lLate <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#007F00">' fill in data from template into output array</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(aT, 2)<br>                aOut(1, i) = aT(1, i)<br>            <SPAN style="color:#00007F">Next</SPAN> i<br>            <SPAN style="color:#007F00">'fill in data from input array to the relevant _<br>             cells of output array</SPAN><br>            aOut(1, 3) = aIn(lA, 6) <SPAN style="color:#007F00">'Fx to Cx</SPAN><br>            aOut(1, 4) = aIn(lA, 7) <SPAN style="color:#007F00">'Gx to Dx</SPAN><br>            aOut(1, 5) = aIn(lA, 5) <SPAN style="color:#007F00">'Ex to Ex</SPAN><br>            aOut(1, 13) = aIn(lA, 3) <SPAN style="color:#007F00">'Cx to Mx</SPAN><br>            aOut(1, 15) = aIn(lA, 1) <SPAN style="color:#007F00">'Ax to Ox</SPAN><br>            aOut(1, 16) = aIn(lA, 9) <SPAN style="color:#007F00">'Ix to Px</SPAN><br>            <br>            <SPAN style="color:#007F00">' print output array to next free line in MRefo</SPAN><br>            rB.Offset(lB, 0).Resize(1, <SPAN style="color:#00007F">UBound</SPAN>(aOut, 2)) = aOut<br>            lB = lB + 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> lA<br>    <br>    <SPAN style="color:#007F00">'clean up</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rA = <SPAN style="color:#00007F">Nothing</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rB = <SPAN style="color:#00007F">Nothing</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rC = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[sijpie]

Forgot to say, because the range to be dealt with is relatively large, I use arrays instead of reading constantly form the sheet. Say that 500 rows meet the date criteria, than you would have at least 18000 reads to do and 8000 writes.

Now I read 5 times and write 500 times for the same 500 rows, using arrays - a massive 25 times speed improvement!

Could do even better, but ...