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

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" <saurabhk...@gmail.com> 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