I have a similar problem to one previously resolved here, however my problem is a little more complex.
The first sheet of my workbook is a summary sheet; it has 27 rows, the first being a header row. Column A then has labels A down to Z. The workbook has a further 26 worksheets, labelled A to Z. Columns B to H of the summary sheet pick up data from the A to Z worksheets via vlookup formulae.
I then have a folder which normally contains 26 .csv files, labelled A.csv, B.csv, C.csv . . . and so on through to Z.csv. Currently I have to open each .csv file and copy the data across to the respective worksheet in the workbook. I want to automate this process.
Ultimately I'll need a loop to work through the workbook but as a first step I've been trying to open the A.csv file from the A tab of the workbook by referring to the sheet name and using a variable 'xyz' to name the .csv file:
xyz = ActiveSheet.Name
My thinking has been that this will become part of the eventual loop. However, even a step such as this seems to be beyond my coding skills; I get error messages no matter what I've tried.
Could one of you VBA gurus out there suggest how I should be coding this? Help on getting the .csv file to open by using a variable to reference the .csv file will be most welcome.