Hello all,
I am trying to copy rows that match a date range but I only want some of the columns to be copied to a pre-defined sheet.
For example, if the row is selected as a match because it is within the desired date range, I want the data in columns 'A-F', 'I-J','N-O' to be copied to the sheet 'Report' starting in 'A3'.
Thanks in advance!!
Andrew
Last edited by drewship; 06-04-2009 at 02:57 PM.
I'm no expert, but I think it may be easier to copy the whole row then delete the unwanted columns. Here's what I did...
Please Login or Register to view this content.
Thanks JP Romano. I was going to look at something like that as a last resort. The report sheet already has the header info and some additional calculation fields I am trying to keep intact. Every time I have to fool with the calculation fields it is a pain... I have several different reports I have to do this to.
If you'll click on GO ADVANCED, use the paperclip icon to upload a sample workbook showing us a good set of sample data and your report sheet.
Also, make sure you've decided HOW to designate the date or date range to match. Is it cells? Is it a popup question by the macro itself? Let us know.
The copy part is easy, just need to see the specifics.
Last edited by JBeaucaire; 06-01-2009 at 01:42 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Also, during the copy job, should the data in the report be completely replaced or just appended to the bottom?
You mention other code/formulas...anything in the Report sheet we need to take into account? Any formulas that need to be extended. Be thorough in your sample.
JBeaucaire, I have uploaded my workbook. There are 4 Report sheets that I have manually assembled. On the Utilities sheet, there are buttons for each report that currently copy all the rows/columns of the matching data to a new sheet. This is good for some of our requirements, but the 4 report sheets represent other requirements.
I am using a userform which is called by the macros on the Utilities sheet to collect the date range desired for the reports and this currently works to copy the matching rows to a new sheet named via the userform.
From the Distribution sheet, I need just matching rows and columns 'A-F', 'I-J', 'N-O' to be copied to the respective report starting at A3 and overwriting all previous data.
The formulas already on the report pages in column 'O' and 'Q' need to calculate after the new data has been copied over.
I hope I have explained this well enough. Thanks!!
Andrew
Distribution formula in column J corrected to make absolute references so it doesn't break when copied to a different column during macro...J3 copied down:
=IF(OR(NOT(ISNUMBER($F3)),ISNA(MATCH($E3,Totals!$A:$A,0))),"",$F3*VLOOKUP($E3,Totals!$A:$C,3,0))
Your sheet was too massive to really pull apart. Instead, I just created a simple macro to grab the data from Distribution between two dates entered and put them on the In_Progress_Report sheet as you had described.
I figured this was enough for you to see a way to copy just the rows you wanted, and perhaps see another way to grab data en masse from one sheet and put it in another without loops.
Hope this helps.Please Login or Register to view this content.
Last edited by JBeaucaire; 06-02-2009 at 09:51 AM.
I suppose if we really want to show off, we can replace these three copy lines of code:
...with this ONE line:Please Login or Register to view this content.
Please Login or Register to view this content.
Thanks JBeaucaire...I love it when you show off
I will plug this in and see how it works...it sure looks impressive enough and I can even understand it even though I could not have come up with it!!
Andrew
Still trying to combine the new code with what I already had. Not able to filter out the non-selected rows yet so may try to copy everything to another temporary sheet, process it, then copy the results back to the report sheet and delete the temporary sheet.
Andrew
The SpecialCells(xlCellTypeVisible) can only copy the visible rows. Not sure how your mileage would vary.
If the one-liner is too confusing, then staying with the three separate copy lines at least has the benefit of being visually simple.
I have it working. I hacked up your code a bit but basically when a report is run from the Utilities sheet, a new sheet is created with the name provided in the userform (just leave it as the default since it gets deleted anyway), the code I was using originally compares and copies all the matching data to the new sheet, your code clears the report columns, copies the designated columns to the report, enters the selected dates, and adjusts the column width.
The code probably looks ugly but it works.
Thanks,
Andrew
Code is for hacking, eh!? Glad you got it working. Onward and upward.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks