I have a macro written that takes code from one book and transfers it to another. The problem is I need to be able to run this macro on over 90 different workbooks. Do I need to write a different macro for each workbook or is there a shortcut I can use?
luke.guthrie,
Here's one way:
Please Login or Register to view this content.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
I am now having trouble with the macro. I need the following columns to go from the workbook "Test" into "FUPLOAD Template"
1. "Test" -M6 through X6 need to go into H on "FUPLOAD" (corresponding to the amount in each month)
2. L on Test - K on FUPLOAD
3. B on Test - N on FUPLOAD
4. D on test - P on FUPLOAD
5. I on test - W on FUPLOAD
6. M8 through X (last) on Test - V on FUPLOAD
7. C on test - O on FUPLOAD
I am not very good with VBA
Here are the workbooks
luke.guthrie,
I think this is the code you're looking for. Make sure that all of the workbooks that you will be copying data from are closed prior to running the macro. This macro should be stored in a standard module within the FUPLOAD workbook.
Please Login or Register to view this content.
This is a little confusing. here is the code I have right now. I am only missing the amounts and corresponding dates and can't figure that out. The last section of code M8:X8 isn't working.
I also don't know how to properly insert code into this. Sorry.
Please Login or Register to view this content.
Last edited by arlu1201; 05-17-2012 at 12:58 PM. Reason: Code tags.
luke.guthrie,
In the future, please wrap your code in code tags per the forum rules (link in my sig for how).
Attached is a modified version of the FUPLOAD Template workbook. It contains a button 'Import Data' that is assigned to the macro I posted earlier. Just click the button and then select the folder that contains the test file. Make sure the test file is NOT open before you run the macro.
Last edited by tigeravatar; 05-16-2012 at 04:19 PM. Reason: Had to reupload attachment
Works perfectly. My only problem is I need to open these files and upload the data one at a time. Is this possible?
luke.guthrie,
To only do 1 file at a time, the code would look like this:
Please Login or Register to view this content.
Run-time error '9'
Subscript out of range.
The following line is highlighted.
ReDim arrData(1 To 16, 1 To Evaluate("CountA(M8:X" & lRow & ")"))
figured it out, nevermind. Thanks for your help!
The only problem is the date is being copied incorrectly.
For example,
March 1, 2013 is showing up as March 13, 2012 after the upload
Try changing them from .Text to .Value2 and then applying the date format at the end, like so:
Please Login or Register to view this content.
That still returns the wrong year. Also I should note the format I need it: 20130301 (YYYYMMDD)
You can change the numberformat to desired. It would simply be: "yyyymmdd"
When copy/paste manually, does it still return the wrong year? because .value2 should ignore date formatting and regional settings. If that is returning it wrong, I would look at the data that is being copied over. When I ran a test, it brought in the correct dates for me
If I copy and paste it the date is correct. What changes need to be made to the code?
In the formula bar I am seeing the correct date but not in the cell
Sorry ignore that post it is coming over incorrect
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks