Make Macro Run Faster

Hey guys. Thanks for taking time to view this thread. This is my first question post, so if I mess something up please let me know.

Here's an overview of my issue: I have lengthy macro that work perfectly yet takes a while to execute, and I would like to restructure it to execute faster (whether that be deleting unnecessary lines or writing a new macro entirely).

Further below in this post is the code that I put together to import data from numerous workbooks (each workbook is named "JSRYYYYMMDD.xlsx", where JSR stands for the report type, YYYY is subbed for the year, MM is subbed for the month, and DD is subbed for the day) into one workbook entitled "The Import Book.xlsm".

The numerous workbooks which the data is pulled from are stored in a folder, and look like this:

JSR20140203.xlsx (this is the Job Status Rollup for February 3, 2014)
JSR20140204.xlsx (this is the Job Status Rollup for February 4, 2014)
JSR20140205.xlsx (this is the Job Status Rollup for February 5, 2014)
JSR20140206.xlsx (this is the Job Status Rollup for February 6, 2014)
(etc.)

When the macro is run, it searches the directory I specified, finds the first file I specify in a cell, copies and pastes over the data I need, closes that file, then moves on to the second file that I've specified and repeats the process until the last file I specify has had its data imported.

The code you see below runs for each and every file imported. It takes about 10 seconds for the data from one file to be imported to the destination file. So, how can I edit the code below to speed up this process?

Let me know if I've made anything unclear. Thanks in advance for all the help.

CODE:

Please Login or Register  to view this content.

Hi, quepes13,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger

Welcome and firstly put code in code brackets so it's more easily discernable ([/code] to end and

Please Login or Register  to view this content.

downside is you get itchy not seeing anything happen.

oh, and you can use this to Alt-Tab between windows without having to designate exactly which window you're switching to. I dunno if that goes any faster or not tho.

Please Login or Register  to view this content.

One of my macros takes a good 40-50 seconds to run partially becaues the file it gets data from is on a network drive. That's if I'm only getting data for one day.

Hi, quepes13,

you should not switch between the worbooks but use object to get the data over from one book to the other:

Please Login or Register  to view this content.

Ciao,
Holger