 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2008Taking info from all sheets and compiling it without spaces
I am a macro newbie so bear with me... what I'm trying to do in this macro is be able to take all the information in a series of worksheets and compile it into one master worksheet. I want to be able to edit and add things to the individual sheets, and with the help of a macro, be able to automatically update the master sheet (by pressing a button of some sort). I know how to tell a macro to take a designated range from one sheet and paste it onto the other, but I don't know how to code it to take all the rows and columns with values in the cells. I.E. I don't want any blank cells in my master sheet and I dont' want to have to constantly update ranges in the macro. Any and all help is greatly appretiated, thanks! From what you say at first, you want a simple copy of the other sheets placed in the master sheet. But then you say that you don't want any blank cells in the master sheet, so that negates the copy idea. Post back and detail the conditions under which you want things copied. In other words, when you click on the button you want this and that copied, but you don't want this, this, this, nor this copied. Provide a few examples to show the pattern. HTH Otto >I am a macro newbie so bear with me... what I'm trying to do in this macro >is [quoted text clipped - 11 lines] > I dont' want to have to constantly update ranges in the macro. Any and all > help is greatly appretiated, thanks! Good questions, my apologies I should have been clearer. Basically I want the macro to recognize how much information is in each of the individual sheets, and copy that information to the master sheet. For example, if I update or add some information in one of the individual sheets, I want to be able to press a button on the master sheet to 'update' what has been changed i.e. expand the range taken in the macro to include the new information. I hope this is specific enough.. > From what you say at first, you want a simple copy of the other sheets > placed in the master sheet. But then you say that you don't want any blank [quoted text clipped - 18 lines] > > I dont' want to have to constantly update ranges in the macro. Any and all > > help is greatly appretiated, thanks! You're back to copying a range and I thought you said you didn't want that. Maybe I'm off track with this. Say that your range is now A1:X100. Are you saying you want A1:X100 copied, AS IS, regardless of what is and isn't in A1:X100 (blanks and all)? Or do you want A1:X100 copied/pasted (or not copied/pasted) row by row depending on some row by row condition? Another question: If A1:X100 is copied, where in the Master sheet do you want it pasted? And if you add row 101 to your data later, where do you want A1:X101 pasted in the Master sheet? Below what's already there? In place of what's already there? If the range copied/pasted from sheet AAA yesterday is smaller than the range being copied/pasted today, do you want the current data in the Master sheet moved to make room for the larger range to be pasted? Suppose today's range is smaller? It would help if you went through a step-by-step explanation of how you would do what you want done if you were doing it all yourself and you were doing it all manually. Otto > Good questions, my apologies I should have been clearer. Basically I want > the [quoted text clipped - 39 lines] >> > all >> > help is greatly appretiated, thanks! Sorry I really don’t know much about macros which is making explaining this hard... What I am doing exactly is taking the client lists of all my co-workers and compiling them into one master sheet. The reason I need macros is because I want this document to be present on everyone’s computer so when I edit in the additional information on the individual sheets, they will be able to look at the master sheet and see all the information easily by just updating it. Here is a step by step example of what I want done: I have two people working for me, worker ‘A’ and ‘B’. Worker A has 100 clients and B has 200 clients. On A’s individual client sheet, his client information covers the cells A1:H100. And on B’s individual client sheet he will cover A1:H200 (same information across the columns). On the master sheet, I want A and B’s clients to ‘stack down’ so to speak. As in, A’s clients will be first and fill the cells A1:H100 and B’s clients will cover A101:H300. If I were to ADD a client (new row) to A’s list, nothing would be updated on the master list because that extra row would not be included in the range taken. So then I would want the macro to be able to expand A’s range to cover that extra row and move everything down after it. The ranges for the two workers on the master sheet would then be A: A1:H101 and B: A102:H301. So at that point, everyone that works for me would be able to see an updated list they could reference and not take eachothers clients etc. Thanks so much for reading this far, help is really appretiated! > I am a macro newbie so bear with me... what I'm trying to do in this macro is > be able to take all the information in a series of worksheets and compile it [quoted text clipped - 6 lines] > I dont' want to have to constantly update ranges in the macro. Any and all > help is greatly appretiated, thanks! Now we're getting somewhere. There is one sticking point. In the Master sheet, it would sure be helpful if there was something, maybe in Column A, to show where A's clients end and B's clients begin. I understand that place is not fixed and must move as clients come and go, that's OK. The problem I see is that when the code goes to copy/paste A's or B's or C's or whoever's clients, it has to find the beginning and the end of those clients as they exist on the current Master sheet. Then the code can move things as necessary to create the space necessary to paste the list of clients. I envision something like this: A's name would be in A1. A's clients would start in row 1 in Column B, C, D, whatever columns are necessary for the client data. A's clients go to, say, row 100. Maybe have a blank row to separate things. This is not necessary for the code. Just a suggestion. B's name would be in A102. B's clients would start in row 102 in Column B. And so on. This is your business so get it the way you like it, but the code does have to have a way to find A's clients, B's clients, and so forth. How many columns go with each client? Do you have any headers in any of the sheets? Otto > Sorry I really don't know much about macros which is making explaining > this [quoted text clipped - 47 lines] >> all >> help is greatly appretiated, thanks! Yes! Sounds like we are on the same page. It seems like the big obstacle here is coding where to find when A's clients stop and when B's clients should start and so on for all our salespeople. There are 12 columns of information for each client with the same info in each column so everything lines up. Spacing a row in between each rep is a good idea, something I'll probably end up doing. However the real problem is finding the code for this.. maybe something like an 'Ifelse' command or something else... (took a VB course in college... of course I forget it all). Also, there are no headings on each of the client lists, just the raw data. I will put a heading in the master sheet though to display which column is what etc.. Thanks a lot for working with me on this > Now we're getting somewhere. There is one sticking point. In the Master > sheet, it would sure be helpful if there was something, maybe in Column A, [quoted text clipped - 70 lines] > >> all > >> help is greatly appretiated, thanks! Getting the code is not the real problem. I can furnish that. The real problem is how to structure your data in the Master sheet so that ANYONE, not just the code, can find the beginning and end of each rep's clients in the Master sheet. I made a suggestion in my last post on how this could be structured. What do you think about that or can you come up with some other structure? Otto > Yes! Sounds like we are on the same page. It seems like the big obstacle > here [quoted text clipped - 111 lines] >> >> all >> >> help is greatly appretiated, thanks! Hello, I am also facing same type of problem :) The formate of the master sheet is like in the 1st row i want the information abth the sheet whether it is sheet 1 or sheet 2 data.Then i want a gap between each sheet in the master sheet and the formate of data in each sheet is same. like ex:Sheet 1 contain data like client name,place,project,date of compltion etc. and all the sheet contain the same type of data. Thanks in advance > Getting the code is not the real problem. I can furnish that. The real > problem is how to structure your data in the Master sheet so that ANYONE, [quoted text clipped - 117 lines] > >> >> all > >> >> help is greatly appretiated, thanks! Rinku I assumed all the sheets have headers in row 1. I assumed the master sheet is named Master. I assumed you have 10 columns in each of the sheets to be copied. This code places the sheet name in Column A starting with A2. It then places all the data in that sheet in Column B, starting in B2. It then places the next sheet name in Column A, 2 rows below the data from the previous sheet. It repeats this for every sheet in the file except the Master sheet. Come back if you need more. View this post in full screen to avoid word wrapping. Otto Sub UpdateMaster() Dim ws As Worksheet 'Utility worksheet variable Dim Dest As Range 'The cell in Col B of Master sht in which to paste If Range("A" & Rows.Count).End(xlUp).Row > 1 Then Range("A2", Range("B" & Rows.Count).End(xlUp).Offset(2, -1)).Resize(, 11).ClearContents End If Set Dest = Range("B2") For Each ws In ActiveWorkbook.Worksheets If ws.Name <> "Master" Then Dest.Offset(, -1).Value = ws.Name With ws .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 10).Copy Dest Set Dest = Range("B" & Rows.Count).End(xlUp).Offset(2) End With End If Next ws End Sub > Hello, > I am also facing same type of problem :) [quoted text clipped - 163 lines] >> >> >> all >> >> >> help is greatly appretiated, thanks! Look at this macro. I just did this for Rinku and I think it might work for you too. Otto Sub UpdateMaster() Dim ws As Worksheet 'Utility worksheet variable Dim Dest As Range 'The cell in Col B of Master sht in which to paste If Range("A" & Rows.Count).End(xlUp).Row > 1 Then Range("A2", Range("B" & Rows.Count).End(xlUp).Offset(2, -1)).Resize(, 11).ClearContents End If Set Dest = Range("B2") For Each ws In ActiveWorkbook.Worksheets If ws.Name <> "Master" Then Dest.Offset(, -1).Value = ws.Name With ws .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Resize(, 10).Copy Dest Set Dest = Range("B" & Rows.Count).End(xlUp).Offset(2) End With End If Next ws End Sub > Yes! Sounds like we are on the same page. It seems like the big obstacle > here [quoted text clipped - 111 lines] >> >> all >> >> help is greatly appretiated, thanks! It is working thank you very much otto. > Look at this macro. I just did this for Rinku and I think it might work for > you too. Otto [quoted text clipped - 133 lines] > >> >> all > >> >> help is greatly appretiated, thanks! Thank you so much Otto for this code. This helps a LOT > Look at this macro. I just did this for Rinku and I think it might work for > you too. Otto [quoted text clipped - 133 lines] > >> >> all > >> >> help is greatly appretiated, thanks! hi it might help you thanks to Ron http://www.rondebruin.nl/tips.htm Ren > I am a macro newbie so bear with me... what I'm trying to do in this macro is > be able to take all the information in a series of worksheets and compile it [quoted text clipped - 6 lines] > I dont' want to have to constantly update ranges in the macro. Any and all > help is greatly appretiated, thanks! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.