Import specific cells from a user chosen spreadsheet

Hi, I need some help. I need to import some spreadsheets that are sent to me into a master database spreadsheet. I have attached both below. The complaintstest.xls is the master database and the other is the sheet that will be imported. What I would like to happen is when the macro is run, the user will be able to select the sheet they would like to import through a dialog box. After the sheet is selected the macro will import the cells from the selected sheet into the master database in a particular order. In the master database example on row 2 I have listed the columns from the sheet to be imported in the corresponding column on the database sheet. This will macro will be run a couple of times a week so the macro will have to find the next empty row to start the import on. If you have any questions, please let me know!

Thanks!

Hello dcgrove,

Can you post a copy of the ComplaintsTest workbook in 2003 format?

Hello dcgrove,

I found the Invoice number on the ComplaintsTest worksheet 2009, but no Credit Invoice Number. Did you miss adding a column for this entry?

Hello dcgrove,

Thanks for the update. Is the PFG Credit number the same as the Credit Invoice number?

Hello dcgrove,

The macro will display the Open File Dialog to let them choose the file to open. A UserForm is displayed to inform the user to select the opened workbook and activate the sheet to import. Once that is done, go back to the master workbook and click continue. The macro will then import the data to the next available row in the master workbook. The selected workbook is then closed after the data is imported. Here is the macro which has been added to the attached workbook.

Please Login or Register  to view this content.

Hello Clayton,

I downloaded the file and ran it. It starts with row 3 and adds to the next available row. Even with data in row 3 at the start, it started copying the data at line 4. No data was overwritten. I ran this on Excel 2003. Which version of Excel are you using?

Hello Clayton,

The columns were offset because you now have 11 columns in Credits requested instead of 10. That has been fixed. Do you not want the copied data to start at row 3? I used row 3 because the master file has auto-filters in row 2. I will change the starting line to whatever you want. Here is the updated macro. Changes are in blue font.

Please Login or Register  to view this content.

Hello Clayton,

I found the problem with the starting row. Use this version and not the previous one I posted. Here is the macro code. Change is blue font.

Please Login or Register  to view this content.

Hello Clayton,

The array called Data holds the column letter for the destination cell. This array makes it easier to map the destination cells to the source cells. There are currently eleven columns used. The array is zero based. The first element is 0, the second element is 1, etc. The statement below copies the contents of the source cell "A1" to the column "G" of next available row, N, on the destination sheet. For this example, N will be 3.

Please Login or Register  to view this content.

The column argument of the Cells property will accept either a number or letter(s). Numbers are faster in a loop, but letters are more easily understood by people.

Hello Clayton,

The destination is fine. You forgot to extend your source range...

Please Login or Register  to view this content.