PDA

View Full Version : Copy range of cells between workbooks with same named sheets



phappu
02-24-2014, 01:05 PM
Hi All,So a bit of vba help please and thanks! I'm looking for a macro that will copy and paste the values in a range of cell from the sheets of one workbook to the sheets of another master workbook. I want a macro that will let me select which second workbook to copy from, and then copy the information from the sheets in the second workbook that are named the same as the sheets in the master workbook.For example:workbooks: MasterWorkbook (MWB), DataWorkbook1 (DWB1), DataWorkbook2 (DWB2)The Master Workbook has sheets named A,B,C,D,E,F,G,H; DataWorkbook1 has sheets named A,C,D,F; DataWorkbook2 has sheets named B,E,G,H.All sheets are formatted the same, the same range of cells needs to be copied for every sheet (should be able to loop this).I want the macro in the Master Workbook to ask me to select a workbook (ie. DWB1 or DWB2) and then copy the information from a specific range of cells the sheets in the data workbook to the sheets with the same name in the master (ie. A to A, then C to C, then D to D, then F to F for DWB1).I'm not entirely sure where to start as the data going from and to the specific named sheet is important and I'm not sure how to do that.Any help would be appreciated!

westconn1
02-25-2014, 01:22 PM
where in the masterworkbook sheet do you want the data to go? replace existing or append?

to loop sheets try like


set wb = workbooks.open("path\workbbookname.xls")
for each sht in wb.worksheets
sht.usedrange.copy workbooks("mwb.xls").sheets(sht.name).range("a1") ' overwrites existing, change range to end of data for append
next
you can probably use application.getopenfilename for a dialog to select file, or if it is only a limited range of files, you could use a list box, with just those files