data population from other workbook based on date

[jmccollom]

Hi all,
A little background on what trying to do. We have some financial sheets in sub folders of the main folder in My Documents. Currently we are copying and pasting Values from the previous year in. What I am trying to do is populate that data automatically based on the month & year of the current workbook.

Any help would be greatly appreciated.

Thanks in advance
Jeremy

 

[ztodd]

Your instructions aren't very clear. Maybe giving us a specific example would help.

 

[jmccollom]

I thought about that last night. Here is the basic paths:

All of the workbooks that working with are in one main folder in My Documents.

My Documents\Reconciliations\(Year)\(Month)

What I am needing to do, for example, is:

In the workbook for October 2014, I need to populate data into it from the workbook October 2013.
The current method using is to open the workbook October 2013, Copy the data that is needed & Paste Values into the workbook October 2014.

I hope this clears up what we are trying to accomplish.

Thank you
Jeremy

 

[jmccollom]

After some more research & trial & error I think I am on the right track but my syntax is wrong. Hopefully this will help explain what I'm trying to do.

We have a template to create a monthly analysis workbook. It contains a sheet for each day (labeled 1,2,3...etc), a sheet named 'Check Register' and a sheet named 'Last Years Numbers'.

Here is the file path:
C:\Users\(Username)\Desktop\Cash Reconciliation\(Year)\(Month).xls

In the past we have gone to the previous years sheet, for example-this month we open the 10/2013 workbook and copy the needed data then "Paste Value" in to the 'Last Years Numbers' sheet of the 10/2014 workbook.

We are wanting to make it more automatic so there is no potential for pasting errors. The date is entered onto sheet '1'!B3.

Here is the formula I think will work with the correct syntax:

=C:\Users|Username\Desktop\Cash Reconciliation\=Year('1'!B3)-1\'[=Month('1'!B3)]Check Register'!G35

Am I on the right track?

Thank you
Jeremy

 

[ztodd]

Almost there with your instructions... What's the format of the file names? For January would it be January.xls? or is it 1.xls? or Jan.xls? ...

Is it always only the "Check Register" sheet you want to copy over?

Be very, very specific- give a very, very specific example- like this- let me know if I'm wrong- is this what you're saying?

Example: Automatically copy the "Check Register" sheet (the whole sheet) in the "C:\Users\jsmith\Desktop\Cash Reconciliation\2013\1.xls" workbook into the "Last Years Numbers" sheet in the "C:\Users\jsmith\Desktop\Cash Reconciliation\2014\1.xls" workbook.

I would make your template into a macro-enabled format or an excel binary format so that it can store a VBA macro. Then you can put a button somewhere that will call VBA code to copy the data for you. Is this an option for if we give you the code and tell you how to do it?

 

[jmccollom]

Currently the name of the files and path are:

C:\Users\Elevator\Desktop\Cash Reconciliations\Cash Reconciliations 2013\Cash Reconciliation 1014 DLH.xls

The file name and path name can be changed easy enough by renaming files and folders. This is the path that I have for the copies to test it on so the originals are not affected.

As for the data that I need copied across, it is not the whole sheet 'Check Register', rather it is 3 sets of data on that sheet that I need to copy just the values to the 'Last Years Numbers' sheet.

I saved the 2 sheets that I am working with to a new workbook if there is a way that I can attach them here I would be glad to share so you can see. I think that would be easier than me trying to explain it.

I was thinking there should be a formula that could work but using code would be just as easy. I have wanted to learn code but just have not had the time.

Thank you for all of your help
Jeremy

 

[ztodd]

Yep, you can use a file sharing website and post a link to your workbook here. Keep in mind that the format of your file names and folders needs to be known ahead of time if you're wanting to automate a process like this. So make a decision on what format they will be and then stick with that. You can't keep changing the way you're doing things, unless you want to always be changing the code or formulas you're using. Which would defeat your purpose of automating it in the first place, which is to save yourself some time and avoid possible errors - right?

And again you'll need to be more specific about where the data is that you need copied over.

oh yeah, and if you're wanting to copy and paste values only, a formula won't be able to do that- which was why I suggested vba code. Is there a reason it needs to be values only? For instance, do the values on the "Last years numbers" sheet need to be editable, after they've been copied over?

 

[jmccollom]

The format of the path, once changed on the host computer, will be a standard. The path format I gave you is where it is on this computer while working on it. The format we will change all the files to is:

C:\......\2014\Jan.xlsx

Here is the link to the 2 pages that working with.

https://www.dropbox.com/s/q17t8pay6xalb8t/CASH RECONCILIATIONS test.xlsx?dl=0

These are the ranges that need to be copied over:

Check Register A60:L93 => Last Years Numbers A2
Check Register A98:G131 => Last Years Numbers A39
Check Register A148:J182 => Last Years Numbers A78


I have been looking some into VBA and I do think it is the best way to go, I am just not familiar enough with it at the moment to try and tackle it alone. As for the pasting values only, it does not need to be editable after they are copied over but the documents do get emailed and the Check Register contains formulas so afraid of data loss.

I do appreciate your help with this. During this process I have been looking at VBA and getting the basics but don't know enough to try and tackle this project alone.

Thank you

 

[ztodd]

No problem- that's what we're here for - here's some code (untested- back up your files first) :

Function copyLastYear()
    Dim fn, y, p, wb As Workbook, ws As Worksheet, dest As Worksheet
    dest = ActiveWorkbook.Worksheets("Last years numbers")
    
    fn = ActiveWorkbook.Path
    p = InStrRev(fn, Application.PathSeparator)
    y = Mid(fn, p - 4, 4) - 1
    fn = Left(fn, p - 5) & y & Application.PathSeparator & ActiveWorkbook.Name
    Set wb = Workbooks.Open(fn)
    Set ws = wb.Worksheets("Check Register")
    
    ws.Range("A60:L93").Copy dest.Range("A2")
    ws.Range("A98:G131").Copy dest.Range("A39")
    ws.Range("A148:J182").Copy dest.Range("A78")
 
    wb.Close
End Function

 

[jmccollom]

Sorry it took a bit to get back. I was out of the office all weekend and in a meeting all day yesterday. I will try this and see if it is doing what we want. I greatly appreciate your help.

Also would you have any suggestions as to where I might go to help me learn more of how to work with VBA? I am interested in learning more but have not found a good place to start.

Thank you again for your help
Jeremy