How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask NewITZone Your Own Question

NewITZone
NewITZone, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 90
Experience:  I have been using Office Pro for 10 years and implemented applications for business use.
53509759
Type Your Microsoft Office Question Here...
NewITZone is online now
A new question is answered every 9 seconds

Need an Excel macro to copy info from several workbooks and

Customer Question

Need an Excel macro to copy info from several workbooks and paste into one workbook.
Submitted: 9 months ago.
Category: Microsoft Office
Expert:  Richard replied 9 months ago.

Hi there and welcome

I can do this for you.

Can you explain what exactly is needed? When you say workbooks, these are seperate files or sheets?

And is it the entire sheet you need to copy in or a range?

Please let me know

Thank you

Customer: replied 9 months ago.
They are separate files but located within one folder. There is a file (Excel sheet) for each sales person that lists the jobs they are paid commission for each pay period. I need this info copied to an Excel sheet in a particular format so it can be uploaded into the accounting program. Attached is an example of a sales person's commission sheet and an example of the format I need the info copied to.
Expert:  Richard replied 9 months ago.

ok, so G and H from the commissions goes into B and C of the QB import sheet? As the sheets have different data

Customer: replied 9 months ago.
Everything that is highlighted on commissions goes into the QB import sheet. The Customer Names go into the import sheet. The $$ in the RTO and Completed Commission columns needs to go into the import sheet on a line that says "prepaid commission" . The $$ in the Expense column need to go on the import sheet on a line that says "Prepaid Commissions:Prepaid Sales Rep Expense Reimb". On the QB import sheet there a transaction for each sales person. The first line of the transaction has the sales person's name, and the next several lines is the list of customers and the corresponding commissions paid.
Expert:  Richard replied 9 months ago.

Can you tell me which Column you mean in the import file with this

The $$ in the Expense column need to go on the import sheet on a line that says "Prepaid Commissions:Prepaid Sales Rep Expense Reimb"

As I do not see that column

Customer: replied 9 months ago.
I need to drive to the office now. I will log back in as soon as I get there so we can continue. A quick phone call might be the best way to explain it. I will log back in approx 30 minutes from now. Sorry, but I don't want to be late to work. I am the accounting manager. Hopefully you will still be online. :)
Expert:  Richard replied 9 months ago.

ok no problem

Customer: replied 9 months ago.
Ok. I am at work.
Expert:  Richard replied 9 months ago.

Can you tell me which Column you mean in the import file with this

The $$ in the Expense column need to go on the import sheet on a line that says "Prepaid Commissions:Prepaid Sales Rep Expense Reimb"

As I do not see that column

Customer: replied 9 months ago.
On the Commission Pay Sheet the $$ in column H go into the QB import file in column H. Column F in the QB import file needs to "prepaid commissions:Prepaid Sales Rep Expense Reimb".
Customer: replied 9 months ago.
On the Commission Pay Sheet the $$ in columns E & F go into the QB import file in column H. Column F in the QB import file needs to say "prepaid commissions".
Customer: replied 9 months ago.
The accounting side of this is the RTO and Completion commissions are labeled as "prepaid commissions" for the corresponding customer name. The expenses are labeled as "prepaid commissions:Prepaid Sales Rep Expense Reimb" for the corresponding customer name.
Expert:  Richard replied 9 months ago.

ok, so your saying Volume H & E & F all go into Column F in the QB import file?

Can I ask please as we initially discussed, give me one clear list of which cells in the commission sheet goes into the QB import sheet please.

Just as Comission H&E&F to QB Import H as totaled as example else the explanation is too confusing sorry,.

Customer: replied 9 months ago.
Sorry, I'm not doing a very good job explaining this in writing. I will try using your example. :)
Commission B to QB Import GExample:
Commission B, E&F to QB Import G&H with F "Prepaid Commissions".
Commission B & H to QB Import G&H with F "prepaid commissions:Prepaid Sales Rep Expense Reimb"
Customer: replied 9 months ago.
Commission G&H added as if both were negative amounts into QB Import H on the first line of each transaction
Expert:  Richard replied 9 months ago.

ok, maybe you need to take a minute and explain it here? As now your saying B which is a date to G which is a currency amount.

Please, I just want you to tell me

Which cells from Comission go to the import sheet.

Don't over complicate it.... just line by line for each one

Commission B to Import G

Comission D + G to Import H

Just like that...

Customer: replied 9 months ago.
I am trying. It's just that on QB Import F needs to auto populate based upon which column the $$ on Commission Sheet.Commission B will always go to Import G
Commission E,F,G,H will always go to Import H
Expert:  Richard replied 9 months ago.

ok, thats much cleaner

So I am adding Commission E,F,G,H togther and putting the total in H right?

Customer: replied 9 months ago.
Not really.
If Commission E or F then Import H and Import F auto populate with "Prepaid Commissions".
If Commission H, then Import H and Import F auto populate with "prepaid commissions:Prepaid Sales Rep Expense Reimb"
Expert:  Richard replied 9 months ago.

ok, so you mean

If Commission E has a value then E else F and put in Import H and in Import F auto populate with "Prepaid Commissions".

If Commission H as an entry, then Import H and Import F auto populate with "prepaid commissions:Prepaid Sales Rep Expense Reimb"

Is this correct and everything?

Customer: replied 9 months ago.
Customer: replied 9 months ago.
A couple more points. On Import on the first line of each transaction
Expert:  Richard replied 9 months ago.

ok, please explain exactly like we just did, all other rules, clearly and per line for each rule please

Customer: replied 9 months ago.
On first line H is the negative sum of H for that transaction.
Commission I1 to Import first line of transaction A
Commission J1 to Import first line of transaction B
Commission K1 to Import first line of transaction C
Commission L1 to Import first line of transaction D
Commission M1 to Import first line of transaction E
Commission N1 to Import I for every line in that transaction
Import first line of each new transaction F will always be "Accounts Payable"
Import first line of each new transaction G will always be empty
Expert:  Richard replied 9 months ago.

commission J, K, L, M, N are all blank in the files you gave me.

Customer: replied 9 months ago.
Sorry. I was doing that from home this morning and forgot it has been updated.
Please see attached.
Expert:  Richard replied 9 months ago.

does every sheet begin on row 6?

Customer: replied 9 months ago.
We have approx. 20 sales people. I checked about 5 or 6 and they all started on Line 5.
Expert:  Richard replied 9 months ago.

ok, last point then is how will you want it to choose the files to import? you run the macro, select the file and it imports, then run it again and it imports the next one and you repeat till its complete?

Customer: replied 9 months ago.
All the files (there is one for each sales rep) are in the same folder "Sales Reps". The macro should run for all files in the Sales Reps folder and create one QB Import file. (Not one QB Import file for each sales rep file)
Customer: replied 9 months ago.
The macro will be run for each paydate so it will need to use the correct range of cells.
Expert:  Richard replied 9 months ago.

ok... what do you mean with the last point?

Customer: replied 9 months ago.
Each commission pay sheet shows numerous paydates. Example: on the Commission Pay sheet the paydates are:
lines 6 to15,
lines 17 to 29
lines 31 to 35
The purpose of this macro is to import the commission info for each pay period as they occur.
Expert:  Richard replied 9 months ago.

ok, thats a new requirement.

I am going to opt out of this question, it may be better suited to another expert to go through and finalize these requirements and gather any additional ones remaining.

You do not need to do anything, you will be notified when an expert takes over.

Thank you

Expert:  The-PC-Guy replied 9 months ago.

i think I was working with you on a similar question before. You decided not to continue at that time

Looks like you want to do this again.

Can I assume that the files attached are the latest.