This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Hi I want to upload to mutiple excel files from Column eg: 4 to 10 and dynamically changing from Quarter to Quarter field values and also want to pick up the data from the starting column A which is the months column .How will I do it through Macro .Can any help ?
I think I've done something similar, though it sounds like you will have to add some steps so that you can change the sheet range as needed (which is why, I suppose, you are looking at a macro).
To get a specific range from an Excel sheet, I first used a Directory tool to get the full path to all the workbooks in my source folder. I then parsed the output field containing the path so I could work with the complete path. You can add a sheet name and range to the path to get what you want.
The formula for my case assigns a new value to the [FullPath] field like this:
[FullPath] is what comes out of the Directory and Parse tools
The rest is in double quotes: first a | character, which Excel requires and then the range syntax in single quotes: 'sheetname$range'
You could then add multiple formulas for each of your use cases and then feed them into a macro that reads the data with a dynamic path string.
You can also query the workbook for the sheet names if you need to get them dynamically.