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

Taking info from all sheets and compiling it without spaces
 

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" wrote in message
...
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!

Taking info from all sheets and compiling it without spaces
 

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" wrote:

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" wrote in message
...
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!

Taking info from all sheets and compiling it without spaces
 

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" wrote in message
...
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" wrote:

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" wrote in message
...
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!

Taking info from all sheets and compiling it without spaces
 

Sorry I really dont 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 everyones 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 As individual client sheet, his client information covers
the cells A1:H100. And on Bs individual client sheet he will cover A1:H200
(same information across the columns). On the master sheet, I want A and Bs
clients to €˜stack down so to speak. As in, As clients will be first and
fill the cells A1:H100 and Bs clients will cover A101:H300. If I were to ADD
a client (new row) to As 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 As 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!

"ZBelden" wrote:

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!

Taking info from all sheets and compiling it without spaces
 

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" wrote in message
...
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!

"ZBelden" wrote:

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!

Taking info from all sheets and compiling it without spaces
 

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

"Otto Moehrbach" wrote:

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" wrote in message
...
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!

"ZBelden" wrote:

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!

Taking info from all sheets and compiling it without spaces
 

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
"ZBelden" wrote in message
...
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

"Otto Moehrbach" wrote:

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" wrote in message
...
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!

"ZBelden" wrote:

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!

Taking info from all sheets and compiling it without spaces
 

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" wrote:

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
"ZBelden" wrote in message
...
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

"Otto Moehrbach" wrote:

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" wrote in message
...
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!

"ZBelden" wrote:

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!

Taking info from all sheets and compiling it without spaces
 

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

"Rinku" wrote in message
...
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" wrote:

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
"ZBelden" wrote in message
...
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

"Otto Moehrbach" wrote:

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" wrote in message
...
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!

"ZBelden" wrote:

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!

Taking info from all sheets and compiling it without spaces
 

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
"ZBelden" wrote in message
...
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

"Otto Moehrbach" wrote:

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" wrote in message
...
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!

"ZBelden" wrote:

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!

Taking info from all sheets and compiling it without spaces
 

It is working
thank you very much otto.


"Otto Moehrbach" wrote:

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
"ZBelden" wrote in message
...
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

"Otto Moehrbach" wrote:

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" wrote in message
...
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!

"ZBelden" wrote:

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!

Taking info from all sheets and compiling it without spaces
 

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

Ren

"ZBelden" wrote:

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!

Taking info from all sheets and compiling it without spaces
 

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

"Otto Moehrbach" wrote:

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
"ZBelden" wrote in message
...
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

"Otto Moehrbach" wrote:

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" wrote in message
...
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!

"ZBelden" wrote:

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!