Problem with macro to copy certain data from multiple worksheets into a summary worksheet

[niftysquirrel]

[Head banging against desk....] I've been working on a spreadsheet for several weeks and I'm down to the very last things that need to be added to get it to do everything I want it to do. Spent the entire day today and yesterday trying to figure this last thing out and it's just not working!! This spreadsheet is the first time I've ever worked with VBA so it's very confusing.

I have multiple worksheets called "Daily_Worksheet0", "Daily_Worksheet1", "Daily_Worksheet2", etc. and the user can keep adding new sheets with a button. Then there's a sheet called "SUMMARY" where all the data from the other sheets is consolidated together. This last part I'm trying to do is take data from all the Daily_Worksheets in cells A41:E47 (which are merged into one cell per row) and J41:L47 (which are also merged into one cell per row) and copy them side by side to a table in the SUMMARY worksheet starting at cell I43. It should be copying the data from each Daily_Worksheet into the next blank cell under the previously added data.

I know merged cells can be an issue but the cells in the Daily_Worksheets need to be merged so I can't undo that. I've tried copying the data right from the merged cells in the Daily_Worksheets to unmerged cells in the SUMMARY worksheet and I've also tried merging cells in the SUMMARY worksheet to match the number of cells merged in the Daily_Worksheets but I couldn't get either to work. What I'm now trying to do is have the code unmerge the cells in the Daily_Worksheets, copy and paste the data to the SUMMARY sheet, and then re-merge the cells in the Daily_Worksheets. Here's the code I have so far. I keep getting an error at "For i = LBound(reMerge) To UBound(reMerge)" (part of some code I found online) and an occasional error in the Paste Special section but it doesn't happen all the time for some reason. Can anyone help???

Please Login or Register  to view this content.

[patel45]

attach please a sample file for testing

[HaHoBe]

Hi, niftysquirrel,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Ciao,
Holger

[niftysquirrel]

Thanks HaHoBe, I didn't know how people were able to do that in their posts. I fixed the code above. I did manage to find an answer to this after much trial and error, FINALLY! This is the new code that I ended up with:

Please Login or Register  to view this content.

It copies the data from each Daily_Worksheet sheet to the SUMMARY sheet no matter how many Daily_Worksheets there are. Next issue though - if a user goes back in and edits any of the info in the Daily_Worksheets that was copied to the SUMMARY sheet, is there a way for the data in the SUMMARY sheet to update with the edited data without copying it to a new row? I can re-run the code above but it just duplicates the same data below the previously copied data.

And is there a way to make all of this run automatically in the background as a user goes through the workbook?

Thanks!!

[HaHoBe]

Hi, niftysquirrel,

the problem of altered, amended or deleted entries was why I suggested to use the Worksheet_Activate-event for the Master Sheet to be used to refreshen all data after the original data has been cleared on that sheet. You may find that thread at http://www.excelforum.com/excel-prog...le-sheets.html.

Ciao,
Holger

[AB33]

You can add these two lines, so that when you run the code, it clears existing data and repopulate with new sheet.
Auto is possible with events code, but I would not do it myself. Have a button and re-run the code when ever you want.

Please Login or Register  to view this content.

[niftysquirrel]

I'm a VBA newbie so I'm not really sure what you mean by "Worksheet_Activate-event" HaHoBe, but I was able to add a button like you suggested AB33 and it's doing what I want. I don't know if I trust the users to hit the button all the time to refresh the data but I guess it'll have to work.

Thanks for all the help!!

[HaHoBe]

Hi, niftysquirrel,

when will you neeed to look at the new data in the summary sheet - at any time you run the macro or when you switch to the summary sheet (thatīs exactly when the Worklsheet_Activate-Event behind sheet summary gets fired)? So you will run the code and then go to summay instead of going to summary and have the code run automaticly - itīs as simple as that.

Ciao,
Holger

[niftysquirrel]

The user should be looking at the SUMMARY sheet at the end after all of the Daily_Worksheets are filled out, but there's always a possibility that they would look at the SUMMARY sheet and then go back and change some of the info in the Daily_Worksheets.

[HaHoBe]

Hi, niftysquirrel,

anytime somebody is looking at summary the code for activating clears the contents and fills the sheet up with the new contents - no pressing of a button needed. If itīs a rather "heavy" workbook a boolean variable may be used to check if anything inside the workbook has been altered in order to run the reorganisation. And for both ways (event or button) the macros need to be enabled while for one you need to activate a sheet (quite natural if you want to look at the data) while for the other you would need to press a button or choose from a menu (if not, the wrong data might be displayed).

From that last statement please judge

Auto is possible with events code, but I would not do it myself. Have a button and re-run the code when ever you want.

I have quite a different opinion on that as I just tried to point out.

Ciao,
Holger