Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Appending Two or More Excel Sheets
Hi,
I have the task of appending some 25 excel sheets into one. And its a tedious process. I am a newbie to the VBA world and still on the bridge .. All I understand slightly is through recording macros and editting them here and there , however the VBA macro language is something I am trying to get my self into. The Problem : I got 25 excel sheets . All formatted the same way . The data for each sheet is in a sheet called dataand starts from row 15 which is the header and from row 16 the data. for each sheet the data is from 50 rows to 200 hundred rows( varies). The rows above the 15th row are titles etc. Now the manual work what I am doing is copy data from one excel sheet to a common sheet ie from row 15 to the end of the data . Copying the header row ( row 15) is only one time. Again opening excel sheet #2 copying the data from row 16 to the end of data and appending it to the common excel sheet. And it goes on with the third and fourth ... etc .I have done that for months now, and I know one could have a walk around through VBA to make life simpler. All I wish to do is dump all those 25 excel workbooks in a folder. I would like to do a macro that would do the job above. By just simply selecting the folder and the sheets would append one after the other , in the common file where the last sheets row ends. If any one has any resource or direction , would really appreciate your help. thanks SD |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Appending Two or More Excel Sheets
Hi,
Go to New Workbook. paste below codings in your macro screen. Activesheet.name="Master file" x=15 pathname = "C:\Documents and Settings\graeme\My Documents\" CHANGE THE PATHNAME BASED ON YOUR REFERENCE filename = Dir(pathname & "*.xls") Do While filename < "" Workbooks.Open pathname & filename Range ("A15").select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy windonws("Master Fiile").activate Range(activesheet.cells(x,1),activesheet.cells(x,1 )).select ActiveSheet.Paste Do until activesheet.cells(x,1)="" x=x+1 loop Windows(Filename).activate Application.CutCopyMode = False ActiveWorkbook.Close (True) filename = Dir Loop Let me know the results. Thanks, Nagu On Jan 25, 10:24 am, "SD" wrote: Hi, I have the task of appending some 25 excel sheets into one. And its a tedious process. I am a newbie to the VBA world and still on the bridge . All I understand slightly is through recording macros and editting them here and there , however the VBA macro language is something I am trying to get my self into. The Problem : I got 25 excel sheets . All formatted the same way . The data for each sheet is in a sheet called dataand starts from row 15 which is the header and from row 16 the data. for each sheet the data is from 50 rows to 200 hundred rows( varies). The rows above the 15th row are titles etc. Now the manual work what I am doing is copy data from one excel sheet to a common sheet ie from row 15 to the end of the data . Copying the header row ( row 15) is only one time. Again opening excel sheet #2 copying the data from row 16 to the end of data and appending it to the common excel sheet. And it goes on with the third and fourth .. etc .I have done that for months now, and I know one could have a walk around through VBA to make life simpler. All I wish to do is dump all those 25 excel workbooks in a folder. I would like to do a macro that would do the job above. By just simply selecting the folder and the sheets would append one after the other , in the common file where the last sheets row ends. If any one has any resource or direction , would really appreciate your help. thanks SD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Appending rows from multiple sheets into one | Excel Discussion (Misc queries) | |||
appending one Excel file to another ? | Excel Worksheet Functions | |||
Appending data from two sheets according to like fields | Excel Discussion (Misc queries) | |||
Appending an Excel File to another | Excel Discussion (Misc queries) | |||
appending to excel file | Excel Programming |