copy sheet by reference number

[pichai]

Hi, my excel file have 5 sheets (may be more than 4) ,sheet 1,2,3,4... and the sheet name (all serch data) .
in sheet 1 ,sheet 2, sheet3,sheet4..... incell a3 is the number for reference . in sheet (all sech data ). i create the macro button and the range (n) of this sheet ,when i key the reference number to the sheet1-sheet4 .
if ,it is the same number to the sheet 1-sheet 4. it will copy the data of that sheets paste to the sheet (all sech data ). if it found the same reference number.
i attach with the file to see ,how can i do for macro copy code??
thank

[Charles]

HI,

See if this is close to what you want.

[pichai]

hi,thank charles for your best idea code.
but let me some more 2 questions.
1.if i want copy to sheet all serch data.. start at row 2 ,how can i modify the code?
2.if my work book have more than that sheets name .say sheet menu ,sheet other ,sheet tel ,sheet abc .in which i do not copy data from that sheets.
i do only copy the some column data from sheet 1,2,3,4.... only from the column b,c,d,f g only to the sheet all serch data start at row 5 by the reference number .
as the eg. how can i modify your code.
thank

[Charles]

Hi,

First off the code does start copying to the "serch data" sheet starting at row 2. However if there is info somewhere else in column "A" then the code will copy to the cell below that info. So you must make sure column "A" is clear except for where the data is to be placed.

Next the code loops thru all work sheets and if it sees the values you entered on the "serch" sheet it will copy the info that I set for the copy and paste.

Now the code can be modified to copy the range you want to row5 of the serch sheet, but it would be easier if the info to be copied were formated to be in the columns that need to be copied. IE: c,d,e,f.

[pichai]

Hi,charles
1.sorry ,for my wrong first question. i want to begin at row 5. so i edit your code at lrow = Sheets("all serch data").Range("A65536").End(xlUp).Row + 4 that'is ok but have the blank row .for eg.row33-35 ,that i do not need it.
2.the second question ,my workbook have more than sheet 1,2,3,4... but it have another sheets name which i do not copy . i want to copy it from sheet1,2,3,4... to the "serch sheet". begin at row 5 .and copy only some column as the example.
let me show you the eg. i do not understand how to edit the code for using with the first and second of my question.
i hope you or any one help me this . by give me the eg.to see
appreciate thank.

[Charles]

Hi,

The following code should eliminate the blank rows.
I or someone will get back to you with the copy range.

Please Login or Register  to view this content.

[Charles]

Hi,

See if this works for you. It should loop thru all worksheets and only copy the range you want.

[pichai]

hi,charles
it work well .you are genius & excellant.
appreciate thank for help.
pichai

[pichai]

Hi,charles
you lastest help code ,that is work well . but now ,i have some more question.
if sheet 1,sheet2 ,sheet3,sheet4.... is the another files are locate at the c:\data , that files name are the same name as the number in cell a3 of each sheets name ,sheet1,sheet2,sheet3.....
when i key in the number in cell n1:n6 on sheet "serch" of my workbook .
and click the button serch. it will open the c:\data , open the file name and copy to my workbook. it can be do or not???
eg. the file name 345,and 145 ,and 222 is locate at c:\data.
for my workbook sheet "serch " , after key in 145 and 222 in range n1:n6.
when i click button .it will copy data 145 & 222 of that file name in c:data
to my sheet"serch" by only column of b ,c,d,f,g

i hope yor oe anyone help me please.
thank

[Charles]

Hi,

If I understand you this code should open and close the workbooks you list in column "N". It should copy only the range specified and close the workbook to get another one.
I set a error trap that if the workbook does not exist to notify you and exit the sub.

Please Login or Register  to view this content.

[pichai]

Hi,charles
thank very much for your help .your code it is perfect ,but may be i show you not clear. it can not copy ,it,msg box "file not found".
so, let's you see the eg. the attach of 2files name 044-3-15843-4.xls and 123-1-03882-2.xls .that files is locate on the c:\data. (may be more than 2 files name).
i want to copy the data from that files reference by the number of range n1:n6 of sheet "serch" . if the number of that files in cell a3 is the same as the workbook name 044-3-15843-4.xls and 123-1-03882-2.xls it will copy the data range of all column a6:h6 to the sheet "serch " of the range begin at row5

2.i want to copy the data from that files reference by the number of range n1:n6 of sheet "serch" . if the number of that files in cell a3 is the same as the workbook name 044-3-15843-4.xls and 123-1-03882-2.xls it will copy the data range of some column of c,d,e,f to the sheet "serch " of the range begin at row5
how can be do ??
thank.

[Charles]

Hi,

The reason you received the error msg was because I set the program to open the workbooks that I assumed were in column "N", but it appears that is not the case. If you want a workbook to open THEN look at column "N" for the data number to be copied from the open workbook you must know the workbook name and where to look in the "all serch data" sheet for the workbook name. Or, have it open all workbooks in a particular folder and check each for the ref nr in column "N".