Excel help? Closed

Question

Hello, 

I have a pivot table that lists items I sell at auction.  After the auction ends I need to produce invoices to all buyers and I want to write a macro from my excel spreadsheet to do this.

All of the data I need to use is on the same workbook, albeit different sheets.

I have a separate tab for buyers details, addresses etc..., the same for vendors, a tab for the auction itself and a tab for Invoices.

At the end of the auction I select a bidder by his unique bidder number from the pivot table and the worksheet displays the items they have won.  Obviously this changes from auction to auction, sometimes only one item, at other times over 10 etc...

The invoice layout is on a seperate sheet and I have used VLOOKUP to successfully enter item details from the auction tab worksheet in the appropriate column on the invoice by reference to the unique bidder id but it is proving a little awkward to do more as the columns are not arranged correctly for VLOOKUP (first column on the left).  Do I have to rearrange these columns  or is there an easier way?

Having selected the bidder I have created a macro button to send the info to the Invoice tab where I can then email, print the invoice.  However, having recorded a macro it is not consistently pasting the info from the column correctly.  I have 21 rows available on the invoice and each bidder has never won more than 12 items at any one auction so no problem there.  However, there are often over one hundred items listed on the auction worksheet and I need a way to select only the items that have been won by the bidder before sending this info to the invoice.  Each entry on the auction worksheet will have a unique bidder ID so easy to distinguish.  I just need something a bit more robust than a recorded cut and paste macro as when you select the items for each unique bidder, it displays those items as the row number that they appear on the auction worksheet.  The macro I recorded only takes the first 21 lines of the worksheet, therefore, if an item appears on row 22 or above it will not be copied over!

Please can someone explain how to perform this simple task?

Many thanks

Andy

 

Configuration: Mac OS X / Safari 534.52.7

rizvisa1 · Answer

Could you upload a sample file at some shared site with you marco and post back the link here, it would be easy to tell you what needs to be changed

Andy · Answer

Hi

Thanks for your quick reply.

I have tried to take a video of my screen to show you how I am recording the macro but the software I have downloaded (Camstudio) is not working!!  I hate having to use Windows as my Mac works perfectly!!

Perhaps I can explain what I have done more clearly.

I have selected the items I want to display on the invoice by selecting just one bidder from the pivot table.
This then shows the lot number of each item won by that bidder and their descriptions, sale prices etc from the various columns of the main spreadsheet.  I then want to transfer this data to the invoice by pressing a button not by cutting and pasting etc. 

I have already successfully used VLOOKUP on the Invoice page to display all of the required fields (description, sale price, commission, total price etc..) by referring each time to the unique Lot number.  
As long as a lot number is pasted or typed into the first column of the invoice page, all other details are displayed correctly = fantastic!

All I want to do is be able to copy and then paste the lot numbers of items won by the bidder into that first column and do so by pressing a macro button, not by cutting and pasting every time.

The macro I have recorded to do this is as follows:
1. Start recording
2. Highlight the first 21 rows of just the first column A which are the lot numbers
3. Right click and hit copy
4. Move to the Invoice Tab
5. Select the 21 rows of the first column of the Invoice sheet.
6. Right click and hit paste 
7. Stop recording

I have assigned a button for this macro and when used it opens the Invoice page and those Lot numbers that have been pasted in the first column have all their respective details displayed in the correct columns = fantastic!

However, when selecting a different bidder from the pivot table in order to produce an invoice for them, they may have won 3 or 4 items that were originally listed on rows 71-74 of the original Auction worksheet.  Now they have only to be invoiced for 3 or 4 items so now problem fitting this info onto the invoice template (21 rows available) but when I use the macro the lot numbers are not pasted across.   This is because the paste function I have recorded selects only rows 1-21 and not 71-74 as needed in this case.  
I appreciate that this may be a complete 'Heath Robinson' way of doing this function but it is almost working and I have no knowledge of VBA writing to come up with something better.  
Ideally this paste function needs to select all items currently displayed in the first column (regardless of what row they appear on in the spreadsheet) and when the button is pressed paste them into the first column of the Invoice sheet so that VLOOKUP continues to work.
Therefore the paste function might read (select (up to a maximum of 21) all items currently displayed in Column A and paste into Column A of Invoice worksheet).

Any clearer?

Thanks in advance

Andy

Andy

Franc · Answer

Hi,
Not sure if this will help in your case or not, but I have a library of Excel Spreadsheets you can download free. If your particular case is not covered, let me know and I will add it.

The site address is

' target='_blank' rel='nofollow'>http://www.workingdata.co.uk/spreadsheet_tips.php</code>



HTH,



Franc.

Excel help?

3 responses

Similar discussions

Subscribe To Our Newsletter!