Need to copy values from all files in folder from specific worksheet

**jh51745** · 05-30-2013, 11:52 AM

Trying to create a macro that will copy data from a specific range (A3:H8) in a specific sheet(Assay Chart) from closed files in the same folder. The files are created off a template where just the data in certain cells change. The data needs to be pasted as values as I am planning on graphing the data on one graph once copied over to my new sheet. If more info is needed let me know. I would like to have the data stacked. I have tried to build one on my own but I am new to creating code and I have not had much success.

**jh51745** · 05-30-2013, 02:19 PM

I found this code that is close to what I need after a couple modifications. But I need it to add the values vertically and not horizontal with the file name preferably in column next to the data while keeping source formatting. Also, don't need to create new workbook and sheet, need to keep in current one. Any ideas?

Please Login or Register  to view this content.

**jh51745** · 05-31-2013, 06:52 AM

Still need help to modify the code I have to go from columns to rows and to paste the values from the formula.

**AB33** · 05-31-2013, 11:32 AM

Try attached.
I am making few assumptions.
You want to copy the file name in column A and the rest of the data in column B.
Your sheet index number is 3.
You can use sheet name instead if you want to use sheet name.

Please Login or Register  to view this content.

You have a header in row 1 in the master sheet and all data are copied in to this sheet

**jh51745** · 05-31-2013, 01:25 PM

Nothing was imported, just says please wait while files are being imported. Is this suppose to be a long process?

**AB33** · 06-01-2013, 05:58 AM

Could you please step over the code using F8 and see what error you are getting? Do you have files on your folder? Does the code open any worksheet?

**jh51745** · 06-03-2013, 07:37 AM

When I step through it opens all the files and looks like it copies but nothing is pasted into master.

**AB33** · 06-03-2013, 07:53 AM

Do you have a header in master sheet? If you do not have, please put one in row 1

**jh51745** · 06-03-2013, 08:11 AM

That did it. Thanks. Any idea on how to switch the columns after I have a chart inserted? First row should be category or x-axis.

**AB33** · 06-03-2013, 08:14 AM

Any idea on how to switch the columns after I have a chart inserted? First row should be category or x-axis?
Not sure I understand your request.
If its is a chart issue, I do not know. If you want to transpose ,i.e. change rows to columns, it is possible.

**jh51745** · 06-03-2013, 08:20 AM

It is a chart issue. For some reason it is not setting my header as my x-axis/category after I have my chart created.

**AB33** · 06-03-2013, 08:23 AM

I suggest you should post it on "charting and pivot table section" of this forum and you will get a speed response.

**jh51745** · 06-03-2013, 08:30 AM

ok Thanks I will mark this portion of the problem as solved.

**jh51745** · 06-03-2013, 11:32 AM

Forgot I need to open all files in sub folders as well. I have looked for ideas but not much luck.

Please Login or Register  to view this content.

**jh51745** · 06-03-2013, 01:05 PM

edited to include current code for file selection.

Need to copy values from all files in folder from specific worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Need to copy values from all files in folder from specific worksheet

Re: Need to copy values from all files in folder from specific worksheet

Re: Need to copy values from all files in folder from specific worksheet

Re: Need to copy values from all files in folder from specific worksheet

Re: Need to copy values from all files in folder from specific worksheet

Re: Need to copy values from all files in folder from specific worksheet

Re: Need to copy values from all files in folder from specific worksheet

Re: Need to copy values from all files in folder from specific worksheet

Re: Need to copy values from all files in folder from specific worksheet

Re: Need to copy values from all files in folder from specific worksheet

Re: Need to copy values from all files in folder from specific worksheet

Re: Need to copy values from all files in folder from specific worksheet

Re: Need to copy values from all files in folder from specific worksheet

Re: Need to copy values from all files in folder from specific worksheet

Re: Need to copy values from all files in folder from specific worksheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions