Select case logical range copy/paste routine

**coasterman** · 09-15-2013, 12:02 PM

I am trying to find the shortest method for users to import their downloaded .csv bank statements into a personal finance workbook.

The column order depending on which bank is involved will differ (and there will be more than needed) so I thought about using a staging sheet to paste into so the user can name the required columns before pasting into the destination sheet.

I have 5 columns in the final destination sheet:

Date, Type, Description, Amount, Running Balance

I have created 10 dynamic named ranges in the staging sheet to accept the raw imported data. (these dynamic ranges are named IMPORTCOL1, IMPORTCOL2 etc..) I have also added data validation drop downs on the head of each import column containing a list of my 5 columns headers in the destination sheet.

What I am trying to do is have code determine which of the 5 needed destination columns the imported column data should be sent to. I know there must be code to loop through each of the 5 required imported columns with values entered in the drop down by the user but for now I would be more than happy to have them execute each column copy/paste routine by a form control button sitting above each import column.

I need to modify this basic code to have it look for the value in the drop down in for example column A (assumes column A is 1 of the 5 to be copied/pasted) and then decide which of the 5 columns in the destination sheet it needs to go to. I suspect this is a Select Case job but I'm unsure how to structure the Case statement? It's been a good while since I've tinkered with any VBA and sadly I've forgotten most of what little I knew. That said if I could get some initial help with this I hope some memory will return

Please Login or Register  to view this content.

Many thanks

**Norie** · 09-15-2013, 12:12 PM

I don't quite see how a Select Case would be used/needed here.

Why not put the 5 headers you want to copy from the import into an array?

Then you could loop through the array and use code, eg Application.Match, to find each header in the header row of the imported data.

When you find a header copy the column from the imported data to the appropriate column in the destination sheet.

**coasterman** · 09-15-2013, 02:11 PM

Thanks for the reply, I've been reading up since the post and am no clearer. I've never used arrays in VBA before so the whilst I have some understanding of them within worksheet formulas I'm at a complete loss in code. I couldn't even find how to manually declare the static array.

I need to narrow my questions as I have many but to focus in on what's needed I think I need a little help to get me started on the code if you have the time.

**Norie** · 09-15-2013, 04:47 PM

Can you upload a sample workbook with the imported data on one sheet and a blank, except headers, destination sheet?

**coasterman** · 09-15-2013, 07:28 PM

Thanks one again for looking.

Hi, I've pared down to what I hope is the minimum needed and recreated the dynamic ranges.

I didn't add the buttons on the import sheet as I think you were suggesting there is a more elegant way than multiple user button presses using an array?