I have a workbook that has 32 sheets in it 31 of the sheets are the same and
the last one is used to track commisions on and do a mail merge to send out
thank you cards. On the sheets labeled 1-31 if cells f 10 - o 11 have
anything other than blank I want cells c 10 & 11(these cells have text in
them) moved to the commision tracking sheet into cell b3 and it will need to
concatenate c 10 & 11. I'am very new to programing. Thank you for your help!

I'm guessing you want to move the data to the commissions sheet starting
at cell B3 and then B4, B5 etc. If so try:

Sub GetData()
Dim comSht As Worksheet
Dim sht As Worksheet
Dim r As Long

r = 3
Set comSht = Sheets("Comms") '<<change to name of commissions sheet
For Each sht In ThisWorkbook.Worksheets
If sht.Name < comSht.Name Then
If Application.WorksheetFunction. _
CountA(sht.Range("F10:O11")) 0 Then
comSht.Cells(r, 2).Value = sht.Range("C10").Value _
& " " & sht.Range("C11").Value
r = r + 1
End If
End If
Next sht
End Sub

Hope this helps
Rowan


jaysphotos wrote:
I have a workbook that has 32 sheets in it 31 of the sheets are the same and
the last one is used to track commisions on and do a mail merge to send out
thank you cards. On the sheets labeled 1-31 if cells f 10 - o 11 have
anything other than blank I want cells c 10 & 11(these cells have text in
them) moved to the commision tracking sheet into cell b3 and it will need to
concatenate c 10 & 11. I'am very new to programing. Thank you for your help!

This worked for one cell and maybe I did not think this through. C10 & C11
are just the first 2 cells in the colum that needs to move. I have C10:C27
that needs to move to the commision sheet in B3, just like you thought below,
if 0. I would also like D10:D27 to move to starting in C3. Maybe I'm
dreaming but I would like a total(which I could hide that colum) starting
with p10:p27 and move it to colum g3 on the commision sheet.

Thank you for your help!

"Rowan Drummond" wrote:

I'm guessing you want to move the data to the commissions sheet starting
at cell B3 and then B4, B5 etc. If so try:

Sub GetData()
Dim comSht As Worksheet
Dim sht As Worksheet
Dim r As Long

r = 3
Set comSht = Sheets("Comms") '<<change to name of commissions sheet
For Each sht In ThisWorkbook.Worksheets
If sht.Name < comSht.Name Then
If Application.WorksheetFunction. _
CountA(sht.Range("F10:O11")) 0 Then
comSht.Cells(r, 2).Value = sht.Range("C10").Value _
& " " & sht.Range("C11").Value
r = r + 1
End If
End If
Next sht
End Sub

Hope this helps
Rowan


jaysphotos wrote:
I have a workbook that has 32 sheets in it 31 of the sheets are the same and
the last one is used to track commisions on and do a mail merge to send out
thank you cards. On the sheets labeled 1-31 if cells f 10 - o 11 have
anything other than blank I want cells c 10 & 11(these cells have text in
them) moved to the commision tracking sheet into cell b3 and it will need to
concatenate c 10 & 11. I'am very new to programing. Thank you for your help!

Hi

Your query was not very clear (or maybe it's just me). In the initial
request you wanted to concatenate C10 and C11. Now you want to move
C10:C27 but I can't see where you want to move this to exactly. Do you
want to concatenate all 18 cells together and place the result in B3 in
the commisions sheet or are you wanting to move them individually to
B3:20. Same for D10:D27 and P10:P27

Try to explain exactly what you want eg:
From sheet 1 check range F10:O11.
If any cell in that range not blank concatenate C10:C27 and move to B3
on Comm sheet.
.....
Move to sheet 2 check range F10:011.
If any cell in that range not blank concatenate C10:C27 and move to B4
on Commm sheet.
etc

Regards
Rowan

jaysphotos wrote:
This worked for one cell and maybe I did not think this through. C10 & C11
are just the first 2 cells in the colum that needs to move. I have C10:C27
that needs to move to the commision sheet in B3, just like you thought below,
if 0. I would also like D10:D27 to move to starting in C3. Maybe I'm
dreaming but I would like a total(which I could hide that colum) starting
with p10:p27 and move it to colum g3 on the commision sheet.

Thank you for your help!

"Rowan Drummond" wrote:


I'm guessing you want to move the data to the commissions sheet starting
at cell B3 and then B4, B5 etc. If so try:

Sub GetData()
Dim comSht As Worksheet
Dim sht As Worksheet
Dim r As Long

r = 3
Set comSht = Sheets("Comms") '<<change to name of commissions sheet
For Each sht In ThisWorkbook.Worksheets
If sht.Name < comSht.Name Then
If Application.WorksheetFunction. _
CountA(sht.Range("F10:O11")) 0 Then
comSht.Cells(r, 2).Value = sht.Range("C10").Value _
& " " & sht.Range("C11").Value
r = r + 1
End If
End If
Next sht
End Sub

Hope this helps
Rowan


jaysphotos wrote:

I have a workbook that has 32 sheets in it 31 of the sheets are the same and
the last one is used to track commisions on and do a mail merge to send out
thank you cards. On the sheets labeled 1-31 if cells f 10 - o 11 have
anything other than blank I want cells c 10 & 11(these cells have text in
them) moved to the commision tracking sheet into cell b3 and it will need to
concatenate c 10 & 11. I'am very new to programing. Thank you for your help!

Rowan,

Here is what I'm trying to accomplish. I have a worksheet for each day of
the month that has to be filled out on a daily basis, on that sheet in cells
C10:C27, each 2 cells have a first and last name in it, therefore the need to
concatenate starting in cell B3 and need to be in the individual cells. So
yes I would like the c colum to move to each cell in colum B on the commision
sheet.

Colums D10:D27 would be the same way because it has the address including
the city and zip code and would be placed starting in cell C3 on the
commision sheet and move down.

The colum P on the daliy sheets could be moved to commision sheet starting
in colum G3 and move down.

The Range of f10:O27 is the critera for the above information to move, if
the cells have any numbers in them that is the factor of "sold" therefore the
0.

Thank you for the help.

"Rowan Drummond" wrote:

Hi

Your query was not very clear (or maybe it's just me). In the initial
request you wanted to concatenate C10 and C11. Now you want to move
C10:C27 but I can't see where you want to move this to exactly. Do you
want to concatenate all 18 cells together and place the result in B3 in
the commisions sheet or are you wanting to move them individually to
B3:20. Same for D10:D27 and P10:P27

Try to explain exactly what you want eg:
From sheet 1 check range F10:O11.
If any cell in that range not blank concatenate C10:C27 and move to B3
on Comm sheet.
.....
Move to sheet 2 check range F10:011.
If any cell in that range not blank concatenate C10:C27 and move to B4
on Commm sheet.
etc

Regards
Rowan

jaysphotos wrote:
This worked for one cell and maybe I did not think this through. C10 & C11
are just the first 2 cells in the colum that needs to move. I have C10:C27
that needs to move to the commision sheet in B3, just like you thought below,
if 0. I would also like D10:D27 to move to starting in C3. Maybe I'm
dreaming but I would like a total(which I could hide that colum) starting
with p10:p27 and move it to colum g3 on the commision sheet.

Thank you for your help!

"Rowan Drummond" wrote:


I'm guessing you want to move the data to the commissions sheet starting
at cell B3 and then B4, B5 etc. If so try:

Sub GetData()
Dim comSht As Worksheet
Dim sht As Worksheet
Dim r As Long

r = 3
Set comSht = Sheets("Comms") '<<change to name of commissions sheet
For Each sht In ThisWorkbook.Worksheets
If sht.Name < comSht.Name Then
If Application.WorksheetFunction. _
CountA(sht.Range("F10:O11")) 0 Then
comSht.Cells(r, 2).Value = sht.Range("C10").Value _
& " " & sht.Range("C11").Value
r = r + 1
End If
End If
Next sht
End Sub

Hope this helps
Rowan


jaysphotos wrote:

I have a workbook that has 32 sheets in it 31 of the sheets are the same and
the last one is used to track commisions on and do a mail merge to send out
thank you cards. On the sheets labeled 1-31 if cells f 10 - o 11 have
anything other than blank I want cells c 10 & 11(these cells have text in
them) moved to the commision tracking sheet into cell b3 and it will need to
concatenate c 10 & 11. I'am very new to programing. Thank you for your help!