Help with VBA code to send values to varying columns in another workbook

[christaylor]

Hi all,
Question:
Anybody have an idea about the VBA code to use if I want to copy a column of data from one sheet in a workbook to paste as values in a master workbook? I wondered if it would be better to try some xml export...though I'm more confident with VBA. Any thoughts gratefully received!
Chris

Details:
Basically this is an activity forecast for different staff and I want to consolidate their estimates of what time they will be committing to various projects.
- individual workbooks will be emailed to staff for them to complete their own data.
- once the data is inputted, they should hit a macro button that sends their data to a central master workbook, saved with a fixed path.
- the data from the relevant column will be pasted into the corresponding column of the master.
- the data from a specific month tab should be sent to the corresponding month tab in the master...sending one month per command, rather than all moths of data at once.
- for any given month, the data in eg column B will only be sent IF the value in B3 is 1.
- the list of projects in column A is standard in both the remote and central versions.

Example:

 

[pbornemeier]

Welcome to the Board!

I recommend that you have code in the individual worksheets that saves a copy of the worksheet in a designated location on a central server whenever the individual worksheet is saved. This would ensure that a user was not trying to update the main file while it was being used. Code similar to this sholud go in the ThisWorkbook code page of each individual file.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ThisWorkbook.SaveCopyAs "\\CARDINAL\Input Directory\" & ThisWorkbook.Name
End Sub

More on using SaveCopyAs is discussed here:
http://www.mrexcel.com/forum/showthread.php?t=244883

You could then use code adapted from here:
http://www.mrexcel.com/forum/showthread.php?t=80641

in the Main workbook to pull data from each of the individual files in the designated location on a central server and place it in the appropriate location in the main workbook.

You could also extract the date/time the files was saved to validate that it was updated within a certain length of time with the FileDateTime function.

 

[christaylor]

Phil, thanks for this idea - I'll give it a go.
Best, Chris