PDA

View Full Version : Auto Transfer



ragamuffin
12-20-2006, 04:52 PM
Hello.

I have a workbook that tallies sales information for individual sales reps and needs to automatically send that information to a summary page. The rep sheet has seven columns of possible information, but the summary sheet needs to only summarize five of those columns, but fill in an additional unique column with the reps name (taken from the sheet name).

I have posted about this workbook before, but my company has just changed the "official" format of part of it - which basically amounts to a complete overhall, which happens to mess up the code entirely and I have to start all over :( .

I have posted a basic version of the workbook, so whatever solotion comes of this, I will probably need your help to expand the code to cover and additional 13-15 worksheets for the real workbook.

Everyone's continued help is greatly appreciated.

ragamuffin

JimmyTheHand
12-21-2006, 02:08 AM
I don't get it.
In Sheets("Totals").Range("B15:F15") you've got those neat formulas and they work, so what's there left that needs to be automated?

One suggestion, though. On sales reps' sheets, in cell B51, I would change the formula to "=COUNTIF($C$2:$C$47,A51)" and use autofill to change formulas all the way down to B118.

ragamuffin
12-21-2006, 11:27 AM
The Totals sheet works fine, but it is the Tracking sheet that needs to fill with data from the rep sheets as well. The Rep sheets have columns for the type of handset, the esn of the handset, type of transaction, timestamp of checkout, esn taken back in (in case of an exchange), the transaction number of the exchange, and if an exchange phone was not available a new phone/"cracked box" was used (all in that order). The Tracking sheet needs to populate with the esn checked out, the timestamp, the sku/type of handset, the esn taken in (if any), and the transaction number. In addition, it should insert the reps name in each row accordingly (the reps name will of course be the name of the sheet - B124 on all sheets). I am currently working on a way to accomplish this without code, but I do not think it will be nearly as clean as using code.

JimmyTheHand
12-21-2006, 12:37 PM
I'm getting closer I think.

Try this code:

Option Explicit

Sub Population_Test()
Dim ShArray
Dim Src As Worksheet, Tgt As Worksheet
Dim c As Range
Dim RowCount As Long, ShCount As Long, RowIx As Long

ShArray = Array("Bob")
'In the array, list all sheet (rep) names, like:
'ShArray = Array("Bob", "Erika", "Evan", "Joe", "Luis", "Priscilla", "Rebecca", "Rosa T.", _
' "Rosie", "Open", "Luke", "James", "Juan", "Maria", "Benita")
Set Tgt = ThisWorkbook.Sheets("ESN Tracking")
For ShCount = LBound(ShArray) To UBound(ShArray)
Set Src = ThisWorkbook.Sheets(ShArray(ShCount))
RowCount = Src.Range("A1").End(xlDown).Row
For RowIx = 2 To RowCount
Set c = Tgt.Range("B" & Tgt.Rows.Count).End(xlUp).Offset(1)
c.Value = Src.Cells(RowIx, 2)
c.Offset(, 1).Value = Src.Cells(RowIx, 4)
c.Offset(, 2).Value = Src.Cells(RowIx, 1)
c.Offset(, 3).Value = Src.Cells(RowIx, 5)
c.Offset(, 4).Value = Src.Name
c.Offset(, 5).Value = Src.Cells(RowIx, 6)
Next RowIx
Next ShCount
End Sub

ragamuffin
12-21-2006, 04:19 PM
Thanks.

I am trying to get the code to work, but getting an error:

Run-time error '9':
Subscript out of range

I am not real sure what this means or how to fix it. I tried replacing the Sheet name with the Index name, but that did not work....

Any ideas?

JimmyTheHand
12-21-2006, 09:20 PM
That message means that you refer to an unexisting element of an array or collection.
E.g. if you don't have a sheet called "ESN Tracking" (case sensitive) in the workbook, this line will break:
Set Tgt = ThisWorkbook.Sheets("ESN Tracking")
Similarly, if you don't have a sheet called "Bob" in the workbook, this line will break:
Set Src = ThisWorkbook.Sheets(ShArray(ShCount))

When you get the error, hit button called Debug and tell me which line of the code breaks the run. (The yellow one.)
Or post the workbook together with the faulty code.

ragamuffin
12-23-2006, 12:18 PM
The error is highlighted on:

Set Tgt = ThisWorkbook.Sheets("ESN Tracking")

The workbook is much bigger than the file size allowed for the forum, so I would have to delete several sheets to post it the whole thing with the full code that you have written...

thanks again for the help.

JimmyTheHand
12-23-2006, 02:40 PM
Well,
Set Tgt = ThisWorkbook.Sheets("ESN Tracking")
is pretty straightforward. If it goes to "Subscript out of range" error, it means that you don't have the sheet called "ESN Tracking" in the workbook where the code resides. Please check the sheet name that it's exactly the same as in the code. The following names are very similar, yet all of them would cause error.
"ESN_Tracking" --> underline instead of space
" ESN Tracking" --> space at the beginning
"ESN Tracking" --> duble space in the middle
"ENS Tracking" --> S and N mixed up

If the sheets name is exactly "ESN Tracking", and it is in the same workbook as the code, then I'm out of ideas. In this case try zipping the workbook , maybe it will small enough to post that way. Or send it to me in email via my user profile.

EDIT
I thought of something. If none of the above works, delete "Thisworkbook" from the code. Although it shouldn't cause problems but who knows. One variable less. For example:
Set Tgt = Sheets("ESN Tracking")
Set Src = Sheets(ShArray(ShCount))

Cyberdude
12-24-2006, 04:10 PM
I haven't really studied what you are doing, but I know that sometimes I get that error if I accidentally have the wrong workbook active. Then of course that sheet I want is not present, and I get the error message. Just to make sure, probably you should add a statement like:
Workbooks("yourworkbookname").Activate
to guarantee the system is looking at the correct workbook when it looks for the specified worksheet.

ragamuffin
12-28-2006, 03:08 PM
Sorry about the delayed response. I have been out of the office over the holidays.

I had to make a split decision on the spreadsheet and make it work the best that I could. SO, I used formulas and filters to transfer and sum all of the data. I really appreciate everyone's helpt on this. I have learned a lot in the process.

Thanks again!

ragamuffin

XLGibbs
12-28-2006, 07:49 PM
YOu can compress the file to upload it if you like...

Right click>Send to> Compressed file. The compressed file should be uploadable..

Feel free to post it up here and ask for help "streamlining" it or optimizing your formulas...(some are memory hogs and can actually increase the file size quite a bit in the process).