Hi, we are trying to build a script that will copy data from two worksheets in a workbook to the corresponding worksheets in another workbook. I am using Excel 2007 on Windows Vista.
The old workbooks have data sorted by date from 2007 to 2009 while the new workbooks have data from 2007 to 2011, and we have to copy the overlapping period - we added two more years. There are thousands of workbooks, and in the process of adding the additional years, we also made changes to the other sheets, one being a chart. All workbooks contain patient samples from hospitals. In this particular example we are looking to copy C7, O7, C8, Q8, C10, F16:AE18, F20:AE20, and F25:AE25.
The old workbooks have different names (i.e. MedRec-LTC_1_HospitalName_0909_OLD.xls) but the new workbooks have a generic name (i.e. MedRec-LTC_1_Generic_NEW.xls).
My initial idea was to hardcode the new workbook name and determine the active workbook name. I tried to only copy the first cell of interest first, but the code does not work:
I tried variations that gave limited success; when I changed the detection of the source, I got it to copy the right cell:
But code did not work for the next step... I patched the code from various bits and pieces of info from various forums, so that makes me think I did not understand the right combination of steps to take; I am not viewing this in the right manner.
Any suggestions or direction?
The old workbooks have data sorted by date from 2007 to 2009 while the new workbooks have data from 2007 to 2011, and we have to copy the overlapping period - we added two more years. There are thousands of workbooks, and in the process of adding the additional years, we also made changes to the other sheets, one being a chart. All workbooks contain patient samples from hospitals. In this particular example we are looking to copy C7, O7, C8, Q8, C10, F16:AE18, F20:AE20, and F25:AE25.
The old workbooks have different names (i.e. MedRec-LTC_1_HospitalName_0909_OLD.xls) but the new workbooks have a generic name (i.e. MedRec-LTC_1_Generic_NEW.xls).
My initial idea was to hardcode the new workbook name and determine the active workbook name. I tried to only copy the first cell of interest first, but the code does not work:
Code:
Sub MedRecLTC2()
Dim WbSource As Workbooks
Dim WbDestination As Workbooks
Set WbSource = ActiveWorkbook.Name
Set WbDestination = Workbooks("MedRec-LTC_1_Generic_NEW.xls")
Workbooks("WbSource").Sheets("Data Entry Sheet").Range("O7").Copy
Workbooks("WbDestination").Sheets("Data Entrey Sheet").Range("O7").Paste
End Sub
Code:
Source = ActiveWorkbook.Name
Workbooks(Source).Sheets("Data Entry Sheet").Range("O7").Copy
Any suggestions or direction?
Last edited: