Taking info from all sheets and compiling it without spaces

[ZBelden]

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!

 

[Otto Moehrbach]

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

 

[ZBelden]

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..

 

[Otto Moehrbach]

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

 

[ZBelden]

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!

 

[Otto Moehrbach]

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

 

[ZBelden]

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

 

[Otto Moehrbach]

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

 

[Rinku]

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

 

[Otto Moehrbach]

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

 

[Otto Moehrbach]

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

 

[Rinku]

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
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

 

[Ren]

hi
it might help you
thanks to Ron
http://www.rondebruin.nl/tips.htm

Ren

 

[ZBelden]

Thank you so much Otto for this code. This helps a LOT