Still need help!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
Click to expand...

Thank you for the help.
 
Sorry, I missed your response in the original thread.

I am still not clear on how you want this to work though. It looks like
you want to take C10 & C11 into Comm!B3, C12 & C13 into Comm!B4 etc and
D10 & D11 into Comm!C3 then D12 & D13 into Comm!C4 etc but then you want
to move column P on each daily sheet to the Comm!CoulmnG. You don't
mention if you want to concatenate these cells though. If you don't I
can't see how you will match up the correct data on each line as you
will be using twice as many rows in column G as you do in Column B and C.

Regards
Rowan
 
Rowan,

You are correct that I want to concatenate the information in cells
c:10&c:11 into Comm!B3, it maybe easier just to wrap the text in comm!B3 if
that is possible, information that is going into that cell is the customers
name (Example Mr. & Mrs John Smith) which may not fit on just C:10.

D:12 & D:13 is the address including city and zip and will not fit on just
D:12 but needs to be moved to Comm!C3 and it maybe easier just wrap the text
in that cell if that is possible (Example 1234 E. Main St Orlando 32808).

Column P may have 2 totals P:10-P:11 which could be added together to go
into G:3
Commision sheet lay out
Col
row A B C D E F G
H I
1 Date Name Address City Zip Type Collected Percent %
2 Sold of Service
Commission Collected
3
4
5
6
etc.

1-31 sheet layout
Row COL
A B C D E
F G H I J
9 Time Of Lead Prospects Name Address Phone Intial Reg
Total Sent Sent
Arrival" Key
Chg PC Install Total


K L M N O
P
9 "Conv & DWT Intial "Reg "Total Total
WDO" Chg" Lawn"


Then starts the regular numbers
10
11
12
13
14
15
through
27

I hope this helps and I'm not sure if this can even be done. It sure would
be nice if it could!

Thank you for your help!
 
Try something like:

Sub GetData()
Dim comSht As Worksheet
Dim sht As Worksheet
Dim r As Long
Dim c 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
For c = 10 To 26 Step 2
comSht.Cells(r, 2).Value = sht.Cells(c, 3).Value _
& " " & sht.Cells(c + 1, 3).Value
comSht.Cells(r, 3).Value = sht.Cells(c, 4).Value _
& " " & sht.Cells(c + 1, 4).Value
comSht.Cells(r, 7).Value = sht.Cells(c, 16).Value _
+ sht.Cells(c + 1, 16).Value
r = r + 1
Next c
End If
End If
Next sht
comSht.Range("B3:C" & r).WrapText = True
End Sub

Hope this helps
Rowan
 
This works pretty well however on the commision sheet it pulls over all the
things put in cells C10-C27 regardless of the cells f10-027 having =0. It
also places 9 lines on the commision sheet.

Thank you for the help.
 
The code is checking that the cells in F10-O11 are blank as per the
original request. If you want to check that the sum of F10-O27 is 0 then
try:

Sub GetData()
Dim comSht As Worksheet
Dim sht As Worksheet
Dim r As Long
Dim c 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. _
SUM(sht.Range("F10:O11")) <> 0 Then
For c = 10 To 26 Step 2
comSht.Cells(r, 2).Value = sht.Cells(c, 3).Value _
& " " & sht.Cells(c + 1, 3).Value
comSht.Cells(r, 3).Value = sht.Cells(c, 4).Value _
& " " & sht.Cells(c + 1, 4).Value
comSht.Cells(r, 7).Value = sht.Cells(c, 16).Value _
+ sht.Cells(c + 1, 16).Value
r = r + 1
Next c
End If
End If
Next sht
comSht.Range("B3:C" & r).WrapText = True
End Sub

Hope this helps
Rowan
 
Back
Top