PDA

View Full Version : Macro for Copying Rows to Another Sheet



ragamuffin
10-05-2006, 02:31 PM
Hello all.

I have a workbook with several worksheets within it - 15 to be exact. The latter 12 are named by individual sales reps. These sheets themseleves populate information on the rep's sales from A2 to G2, and down to row #100. Of course, not all of these rows are filled everyday - usually anywhere from 5-10 are filled. The third sheet in the workbook is named ESN Summary. What I need to do should be simple (I think), but I cannot figure it out. I have tried recording a macro for this, but I cannot get it right...I want a macro that will read only the populated rows from the rep sheets and copy them to the Summary sheet. The summary sheet looks exactly the same, except that all the information that would be copied over would actually move over a column (i.e. the first copy would populate B2:H2), because A2 is an additional column - "Rep" - where I would like the macro to insert the name from the sheet it copies from. (I hope that makes since). Of course, I can filter it and organize it later, but I would like for the macro to run automatically and copy after every entry on the rep sheets, that way all information is always up to date. I also of course need to loose no information, so maybe if the macro can be told to go the next row after every entry, but I think this might make all of my data spread out on my summary sheet, so maybe it would be better to have the macro run from a command key, like Ctrl + t, or something...I don't know....

I really appreciate any and all help!

thanks!

ragamuffin

mdmackillop
10-05-2006, 02:44 PM
Hi ragamuffin,
welcome to VBAX.
Can you create a simplified sample workbook and post it? Add any comments/requirements to the worksheets. Use Manage Attachments in the Go Advanced section
Regards
MD

ragamuffin
10-07-2006, 10:20 AM
Sorry about the delay in responding - it's been a long week!

Thank you so much for the info and advice. I have created a sample spreadsheet and it is attached. I am looking forward to learning anything I can!

Thanks again!

ragamuffin

p.s. I had to alter the original file quite a bit to get it down to an acceptable attachment size, so my actual file has many more worksheets in it.

mdmackillop
10-07-2006, 10:59 AM
Not too many problems here, but as you want data copied from Rep to ESN, we need to determine if data has previously been copied to avoid duplication. Either your data has to contain unique values which can be checked against copied data or we can add a "Transferred" column where a value can be entered to record this (Column can be hidden of course).
Your thoughts?

ragamuffin
10-07-2006, 12:15 PM
Either way sounds like a viable solution. My data does have unique values to an extent. The data validation for columns A,C, and G will of course vary within their specified fields, but the data in columns B, D, E, and F will almost always be different. The ESN columns record unique serial numbers, and of course the timestamp will vary minute to minute. That may more than you needed to know, but there it is anyways...But, the idea of a transferred column is something that I have not thought of. Are there any pros/cons to having it as opposed to having the transfer happen in another way (if another way is possible)?

Cyberdude
10-07-2006, 12:41 PM
I can't get it straight in my mind, but it seems like there might be a way to use formulas to do the copying instead of a macro. A formula would do immediate updating when something is entered. It seems like you ought to be able to write something on your Summary sheet column B like:

= SheetName1!$A$1

I know you have some other stuff to do too, and a macro is probably needed for that. Just thinking.

ragamuffin
10-07-2006, 02:09 PM
I have thought about formulas, but I keep running into an issue of space. If every Rep sheet has 100 rows, and there are (in reality) a total of 12 rep sheets, it seems that I would end up with a total of 1200 rows on the summary sheet (if it is a "cell-for-cell" type of copy)...I guess I could use a filters on the summary sheet, but I think it would still be a little cumbersome keeping the information together...that's why I was leaning more towards a macro in my thinkin too. Something that recognizes on each sheet rows that have information in them and transfers them to the summary sheet (while inserting the name of the sheet in the cell in column A), but once the transfer occurs moves the curser/selection box down one row to the next appropriate box where the paste occurs (columns B:H).

I'm just thinking too......

mdmackillop
10-07-2006, 02:12 PM
Insert a new column A headed Transfer on your Rep sheets and run the following code. I've assumed your summary sheet is sheet 1. You'll need to correct your date entry macros to suit.

Option Explicit
Sub Summarise()
Dim LRw As Long
Application.EnableEvents = False
Application.ScreenUpdating = False
For i = 2 To Sheets.Count
With Sheets(i)
.Columns(1).Hidden = False
.Columns(1).AutoFilter Field:=1, Criteria1:=""
LRw = .Cells(Rows.Count, 2).End(xlUp).Row
chk = Range(.Cells(2, 1), .Cells(LRw, 1)).SpecialCells(xlCellTypeVisible).Count
If LRw > 1 Then
Range(.Cells(2, 2), .Cells(LRw, 2)).Resize(, 7).Copy _
Sheets(1).Cells(Rows.Count, 2).End(xlUp).Offset(1)
Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(chk) = Sheets(i).Name
End If
On Error Resume Next
Range(.Cells(2, 1), .Cells(LRw, 1)).SpecialCells(xlCellTypeVisible) = "t"
.Columns(1).AutoFilter
.Columns(1).Hidden = True
End With
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

ragamuffin
10-07-2006, 02:34 PM
Thanks! But I think I might be doing something wrong...I inserted a new transfers column on the rep sheets, which makes rep sheets 1 and 2 have the header "Transfers" in A1 on both sheets. I left the Summary sheet alone. Have I missed something, because I cannot get the macro to work...

Sorry for my limited understanding.

mdmackillop
10-07-2006, 02:44 PM
Ooops!
I forgot to mention I relocated your validation data. It was interfering with finding the last used row.
Here's my version

ragamuffin
10-10-2006, 01:45 PM
Thanks for you version. I like the validation data relocation! I am going to change it on all my reps on the actual spreadsheet. Thanks for that! I am still not getting the copy feature to work. I have tried pasting the macro you came up with after the timestamp macro (which did not work) and I have now deleted the timestamp macro altogether, but still to no avail. What else could I be doing wrong?

mdmackillop
10-11-2006, 02:49 PM
Here's a "working" version (I hope). I've moved all your validation with summary formulae to a separate sheet. Add some more data before trying the Summarise button.

ragamuffin
10-12-2006, 01:09 PM
It Works! Thanks so much! However, I when I go to view code I cannot see the macro. I'll need to expand the macro for the several other sheets that I have on the actual workbook and insert the names of the reps (instead of having it read rep 1 or rep 2). Also, is there a way to move the Summarize button so that it is not on top of the other information?

Thanks again, you have been a big help!

ragamuffin

p.s. sorry to always have more questions...

ragamuffin
10-12-2006, 01:31 PM
Nevermind my last post. I played around some more with it and found that my issues were easily resolved. I do find that the Summarize button does not work everytime, and I am not sure why? Any thoughts?

Also, where are rows 7-23 on the Summary sheet?

mdmackillop
10-12-2006, 02:17 PM
Also, where are rows 7-23 on the Summary sheet?
Not sure what you mean. They're visible in the example I posted.

ragamuffin
10-12-2006, 03:31 PM
As I add/subtract data, the summarize button does not work all the time. For instance, if I open up your version and begin adding data, then the button works fine, but if I remove the data and make the Summary sheet and rep sheets blank, then data will not copy. Could you explain how you got the copying to work?

I noticed on the Validate sheet there are rows for each rep, but the formulae in there are above my level of comprehension. I have been researching the formulae to try to put it together on my own and I think I have a little better understanding of that, but would still very much appreciate some help understanding it in detail in case I have to edit it later if needs change (I am simply not sure what it is doing and why).

I have also noticed that the Summary sheet only copies new information and, while this can be a benifit since information would not be dublicated, I need it to copy any cells that have been updated, even if the update is a deletion of information (e.g. if a sales rep goes to sell and item, but the sell does not work out and it is returned to inventory, it would be deleted from the individual reps sheet and upon hitting the summarize button, the correct and updated information should populate on the Summary sheet).

Additionally, I noticed that if information is entered on row 6 of a rep's sheet, for example, and then rows are skipped and information is entered on row 22 on the same rep's sheet, the summarize button will copy all of the rows from 9-22, inserting a lot of blank rows on the Summary sheet. I realize that I can use the filters to sort through this information, but it is an extra step that would be nice to avoid.

I also noticed column A with the header "T" and a "t" in every row copied - I am assuming this stands for "transferred" and somehow the formula/macro is reading not to transfer those again. Could you give me more input on column A and how it is working within the sheet?

I am still leaning toward a macro as the best solution, but if it is not possible, then can these other criteria be met?

Thanks again for your continued help!

ragamuffin

mdmackillop
10-12-2006, 03:52 PM
One small thing 'cos it's late here.
The formula on Validate is simply a CountIf function.
To make it flexible, I've added an Indirect to use the column heading instead of typing the name in each cell.
Because the sheet names contain spaces, this requires adding single quotes around the sheet name portion, and these need to be contained in double quotes in order create string which would appear in the original Countif.
It's a bit fiddly, but it lets you add/change names without having to change the formulae. Hope that's clear!

BTW,
Why skip rows when entering data. That's just making life difficult for no good reason.

ragamuffin
10-14-2006, 01:16 PM
Thanks for explaining the Indirect feature. I have not used those yet in my own designs, but I am sure I will find the technique very useful in the future.

To your question, I am not skipping rows intentionally, but sometimes in a line must be taken out and since I keep the sheet protected, I just highlight and use the Clear Contents selection to do the job. So, in your version it does the copying, but from what I can tell it does not update with current info, and in the case where a row was cleared out on a rep sheet, there would either be a blank row copied to the summary with the reps name or the cleared out info would only clear out on the reps sheet and not on the summary sheet (depending on when you press the Summarize button) - so I need the Summary sheet to be able to stay up to date with every click of the summarize button, otherwise it is not reporting true numbers.

I am still trying to figure out column A on the reps sheet and how the the copying actually works, so any help on understanding that would be great!

Again, I am still thinking that a macro would be the easiest fix, I am just unsure how to record/write one that does what I need...

mdmackillop
10-14-2006, 03:17 PM
The Column A method worked by performing an autofilter on the column to return the blanks. these rows were copied to your summary sheet. If you're going to have empty rows, then this method is not so handy. It's possible to still use this method if you perform a sort or run code to delete blanks in order to bring the filled rows together, and for a large number of rows, this may be beneficial.
I've modified the methodology here, moving the indicator to column H and checking each cell in this column, copying the blank cells where column A is also blank.

ragamuffin
10-17-2006, 10:51 AM
That method works really well, because it groups the rows together and adjusts for blank lines. But I still cannot get it to update on the ESN Summay sheet. Once I open the workbook, enter some data, and click summarize - it works like a charm. If I go back and update the data by adding to it, the summary works great again! (AWESOME!). But, if I take data away, the Summary sheet does not update, so the information does not accord with what is reported on the rep sheets. I've tried even clearing the "t" in the transferred column for the row that I have cleared out, but I think the loop here is that the method just sees this as a blank row now and will not transfer over. So, I guess we need a way to read where a row came from and to somehow search back over those rows whenever the summarize button is clicked and to copy whatever is there, even if it is blank.

I've looked at the vba code you've written and it is quite above my level, so my apologies for not being able to dialogue in specifics. I was thinking...is there a way to add in something that works kind of like the timestamp macro that I use.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect([A2:A100], Target) Is Nothing Then
Application.EnableEvents = False
Cells(Target.Row, 4) = Now
Application.EnableEvents = True
End If
End Sub


As you have seen, anytime a cell in column B is changed on any rep worksheet, the time updates in the same row, under column D. If we could somehow expand this methodology to recognize rows (not individual cells) on all the sheets and instead of a timestamp/"Now" feature, use a copy feature (activated automatically with everychange or by keystroke, say maybe Ctrl + t).

Your thoughts...?