I have multiple worksheets in a workbook and all sheets have the same format i.e., number of columns are same. I have to combine all these worksheets into one sheet like 'target' sheet in which the last column of each row displays the location of that row in the source file (from where it was taken) with source worksheet name.
please help me for this which is my research work in chemistry
Hi.
Post a sample data file.
Click *, if my suggestion helps you. Have a good day!!
Try this code - change the bold sections -
Copy the Excel VBA code
Please Login or Register to view this content.
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste
To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
I attached a sample workbook 'fame.xls' which consists three worksheets 1,2,3. 'consolidated' is the resultant required worksheet which is a combining of these three sheets and the last column 'L' indicates the position of each row in the source file (for ex: 1;1 means worksheet file name 1; 1 row). 'consolidated' is the resultant file for that macro or solution is required.
Did you try the code i provided?
I tried the code on real data sir. 'Target' file was created and the last column was displaying the name of the source file but not displaying the location of the row in the source file. I am uploaded the original file in zip format with the macro sir. please help me sir.
Is the zip file in post 6 containing the same file which you uploaded in post 4?
No Sir. In post 4 the data is sample data for testing. In post 6 is the real data on which I am working. The code is working on post 4. For post 6, the name of the file is displaying but the position of the row in the source file is not displaying sir.
Will you be able to upload the file directly? I have an issue with winzip on my system.
Attachment of the file is not accepted through this sir, because the file size is 2.1 MB.
I requested the administrator and he uploded the file like this :www.ctri.org.in//FAME.xls
The file can be downloaded through this link sir.
please help this sir.
You do not need to upload your real file. Just a dummy file with some sample data so we understand what is your data layout.
Sir
I attached the real data file reduced to 50 rows of each excel sheet. Macro also included in this sir. Pls help for this sir
What is the issue you are facing? I ran the code and didnt find any issues.
Name of the source file is displaying for each row in 'Target' file but the location of that row is not displaying sir like 'SN8;5' means source file name is SN8 and 5th row is the position of this row in that file.Originally Posted by arlu1201
Sir. Pls run the code with data.xls. I am getting only source file name in column AA; not getting its position sir. please help me
You want the row number of each row from each sheet to be displayed?
yes sir. the name of the source file with row number
Then the code will need to be reworked. Will be back with a code in some time.
Sir please help for this code sir.
Try this updated code
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks