Import data from other workbook where the import to and from are both variable

[mjtaxpro]

I'm looking for a way to automate some daily tasks. Right now I have about 30 different workbooks, each for a different company for personal property tax returns for 2017. I'll now be starting new workbooks for 2018 personal property tax returns for those same companies. I'm wanting to be able to import the data from 2017 into certain cells in the 2018 workbook. Whatever is in Cell A5 in 2017 may need to go to Cell A6 in 2018, but it would go from that same cell in 17 and to the same cell in 18 for all companies. In other words, the to and from cells will be the same for every company. The to and from workbooks is what is variable though. The 17 workbook may be in C:/ABC Company/2017/17Return.xlsx and the 18 is in

C:/ABC Company/2018/18Return.xlsx

Is there a way to create a macro or vba or something that automates the import. I have excel 2016
​

 

[mumps]

The key in trying to do what you want is how your files are named and where they are stored. Are the 2017 files all in one folder and are the 2018 files all in one other folder? What is the full path of those 2 folders? Can you list 2 or three company file names for the same companies for each year including the extension (xls, xlsx, xlsm)? What is the name of the sheet containing the cells to be copied from 2017 and what is the name of the sheet in 2018 where they are to be pasted? Are the sheet names the same for all files? What are the actual cells to be copied and what are the cells they are to be copied to?

 

[mjtaxpro]

U:/ABC Company/2018/2018 personal property.xlsx
U:/ABC Company/2017/2017 personal property.xlsx

U:/XYZ Company/2018/2018 personal property.xlsx
U:/XYZ Company/2017/2017 personal property.xlsx

That is the general formatting for most companies. Although some of them arent in a separate folder for the year. For example, some of them are just
U:/ABC Company/2018 personal property.xlsx
U:/ABC Company/2017 personal property.xlsx

The sheet name for all companies for all years is: Form PA-003

Cell A9-A13 to Cell A9-A13
Cell F134 to Cell B110
Cell F135 to Cell B111

There is a ton more, but if i get the basic idea, i can copy it.

Thanks!

 

[mumps]

Because of the inconsistency of where the files are stored, it makes the task of finding each file a little complicated. Is there any way of saving or re-organizing the files in 2 separate folders, one containing the 2017 files and the other one containing the 2018 files?

 

[mjtaxpro]

Yes, what if i had one folder for all personal property tax returns for each client. So it would look like this:

U:/ABC Company/Personal Property Returns/2017 personal property.xlsx
U:/ABC Company/Personal Property Returns/2018 personal property.xlsx

U:/XYZ Company/Personal Property Returns/2017 personal property.xlsx
U:/XYZ Company/Personal Property Returns/2018 personal property.xlsx

 

[mumps]

The only way I think that setup would work is if you had a sheet with a list of the company names that could be used as reference. Since you would need a separate workbook from which the macro is run, this list could be in that workbook. I think the easiest alternative would be to have only 2 folders. One would hold all the 2017 files and the other all the 2018 files, for example: U:\2017 Company Files\ and U:\2018 Company Files\. This would eliminate the need to have a reference list. The macro could then loop through all the 2017 files and find the 2018 files based on the name of the 2017 files. Each file, however, would need to have the company name as part of the file name, for example: ABC Company 2017 personal property.xlsx and ABC Company 2018 personal property.xlsx. Which method do you think would work best for you?