Copy/Paste Range to Another Range based on Criteria

[ExcelFinWizzard]

Please see attached workbook. I'm trying to copy the data in cells A5:E150 and paste them into their correct location (based on the month/year selected in the above cells). The matrix where these cells should be pasted begins at cell A200 and extends down/right (I plan to extend this archive of data to the year 2022). Please note: The data should not be pasted over existing data (i.e. someone accidentally selects January 2012, which has already pasted); so there should be a validation box asking something like "Are you sure you want to paste over the existing data for "Month","Year"?". If yes, then it proceeds to paste over the existing data, if No, then it cancels the macro.

Also, I would like to recall prior data for a given year, as you can see a basic setup located in cell H1 and extending down/right. This is there so that the user can review data that was entered for prior months, given the selected year.

Any help would be great...

[StevenM]

In my opinion, it would be much easier if you planed your archiving in simple multiples.

For example, you have the months of the year 2012 starting at row 202, then the months of the year 2013 starting 399. Why? Why not simple multiples of 100? If January 2012 is at row 202, then January 2013 should be 302.

Also, you have January starting at column A, then 7 columns later is February, and 5 columns later is March. This can be worked around, but is it really necessary? Why not have each month 5 columns apart? Or at least the same number of columns apart. If you need 7 for January, why not 7 for the rest?

Simplifying these things would would make writing the code for your archiving all that much simpler.

[ExcelFinWizzard]

In my opinion, it would be much easier if you planed your archiving in simple multiples.

For example, you have the months of the year 2012 starting at row 202, then the months of the year 2013 starting 399. Why? Why not simple multiples of 100? If January 2012 is at row 202, then January 2013 should be 302.

Also, you have January starting at column A, then 7 columns later is February, and 5 columns later is March. This can be worked around, but is it really necessary? Why not have each month 5 columns apart? Or at least the same number of columns apart. If you need 7 for January, why not 7 for the rest?

Simplifying these things would would make writing the code for your archiving all that much simpler.

Sorry, my organization on that framework was a little sloppy. Sure, we can do the years in increments of 100 rows. And the 7 to 5 columns from January to Feb that you were referring to, that just needed brushing up (no logic behind it). This framework is a draft, more than anything I want to know the code to get that data into its correct place and to retrieve it using buttons.

I've attached the updated spreadsheet with those minor changes. NOTE: The initial data entry A5:E101 should not be altered in size, currently it is 97 rows by 5 columns.

Thanks!

[StevenM]

Copy and paste this code into a standard module.
Run: ArchiveData

Please Login or Register  to view this content.

I deleted the rows with pseudo data below the total.

See: ExcelFinWizzard_CopyPasteExample2.xlsm

Here are some thoughts:
I don't think you want borders (lines) drawn in the archive area (they might make copy and pasting more difficult).
Come to think about it. You want six columns between each month. So that there will be an empty column between each month. Once you've worked out these details, it would be a simple matter of writing a sub-routine called: UnArchiveData which would take the archived data and move it into the original spot.
The above code is not perfect. There should probably be more checking. Such as I didn't check to see if the number of rows being copied was over 97 rows. (You don't want the data over writing some other year's information.)
Another idea might be to move the archive data into another worksheet. And then you can hide the archive worksheet. There would be no need to label the archive data with year and month headers since no one will ever see it. You will use a macro to place the data into archive, and use another macro to get it out of archive.

Just some thoughts to consider.

[ExcelFinWizzard]

StevenM, thanks for the code. It works well. As a VBA rookie, I'm having a little trouble interpreting it.

Can you, or anyone else, explain the "As" Long, String, etc... And the nYear, nMonth, nRow, etc. Also, the sAnswer?

Do they simply act as variables defined by the developer?

I'm not finding these in the Excel VBA book Im referencing. (I'm sure they're there, but its 1000+ pages).

Thanks...

[StevenM]

See notes in code.

Please Login or Register  to view this content.

Any other questions, just ask.

I guess you didn't like my idea of archiving the data into a hidden archive worksheet?

[ExcelFinWizzard]

Thanks for the explanation Steven... Yes, I like the idea of using a separate archive sheet. However, I would like the data to be titled (year/month) just in case something breaks and we need to look up the data manually.

[StevenM]

That makes sense. I'm busy today, but tomorrow I'll try to upload a worksheet with a couple macros for you to look at.

[ExcelFinWizzard]

Steven, how would I go about retrieving the data given the year that is being searched for? As the template shows in cell H1.

Also, I'm going to try to keep the data on one sheet for now. If there is a solid reason to put it onto another sheet, I'll try that later.

That's no problem, tomorrow would be great.

Thanks

[StevenM]

I got carried away with an idea which I had.
I know you asked to use just one sheet, but I couldn't resist using a hidden sheet.
(Part of what I do on this forum is for my own self-education.)
Let me show you what I got, and if you don't like it, I'll help you with putting everything on one sheet.
The basic principle is the same.

See: ExcelFinWizzard_ArchiveData.xlsm

One thing you'll notice is that I removed the formatting. In my opinion, formatting comes last. (And I ran out of time. I might be able to address the formatting issue in the next day or so.) First get everything else working, then add formatting.

Also note, there is a macro: ToggleArchive. Run this macro once and it will unhide the archive worksheet. Run it again, and it will re-hide the archive worksheet.

It would also be possible to have multiple archive worksheets, let's say for different companies or accounts.
I haven't had time to do a lot of testing. But I got the basic concepts to work.