extract data from another worksheet

[ramdzan]

I just want to extract the data automatically using macro to save some time and can be use for the same companies next month.
All the data is from the best(details) worksheet. The data is:
acc no
prod
NPL
MIA
OS BAL
IIS
Borrower CIF No / Borr Rating / Coll Rating
Borrowing Limit (per CIF) / Relationship (Yrs) / External rating
Borrower Cost Centre Code / BAC / OIC



Hope someone can help me
I could'nt attach the workbook here
so if someone can give email to me to send the workbook it'll b great

[stnkynts]

Hello ramdzan,

A lot of specifics are missing from your post. What do you mean by "extract"? Where is this data at (ie Row 1)? What do you want done with it? Etc.

Why can't you post a copy here?

If you correct these issues I am sure you might get the help you need.

[ramdzan]

i have attached the file.. please take a look

[MarvinP]

After taking a look - it makes no sense to me.

You have 67 worksheets in this workbook. There is no worksheet called best(details) as you suggest above

All the data is from the best(details) worksheet.

I also don't see a question mark in any of messages. What was the question?

[ramdzan]

im sorry, my mistake.. it's raf details i mean.. the question is i need to put all these data:
acc no
prod
NPL
MIA
OS BAL
IIS
Borrower CIF No / Borr Rating / Coll Rating
Borrowing Limit (per CIF) / Relationship (Yrs) / External rating
Borrower Cost Centre Code / BAC / OIC

If i copy and paste it will be very tedious.. so i want to automate this task
if u see at other worksheet other than raf details, all the data is already been put at specific cells
If get the same info next month with differen numbers, i don't want to copy and paste anymore, so i need to automate this task,

[MarvinP]

HI,
After much study, I see you have a real problem. With 57 sheets you need to pull data to the first sheet. Some parts of the 57 sheets are the same but there are some differences also. Here is the plan.
Your sheets are numbered from 1 to 57 from left to right. So Sheet1 = details sheet, Sheet2 = AYI and Sheet 9 = Bang etc.

I have written a Function that will help you grab data from any of these sheets. Here it is:

Please Login or Register  to view this content.

Here is how the above works. Looking at Sheet 2 = AYI if you wanted to grab the 1540 in H6 you would type in a cell in the details sheet

Please Login or Register  to view this content.

This function takes the sheet number (2)
The text you are looking for or "Borrower Cost Centre Code / BAC / OIC"
The rows below or above the text (0) as it is on the same row
The columns to the right of the text (6) because the 1540 is 6 columns to the right.

If you were to change the 2 to a 3 in the formula above it would grab the same cell from sheet3.

This is about half of your problem.

The second half is the account numbers. There might be only one or many. I've written a subroutine for this that puts the range of accts in the clipboard so you can paste it.

Here is that code:

Please Login or Register  to view this content.

You need to call this code like:

Please Login or Register  to view this content.

This will go the the 5th sheet (XAL Learng) and find the account numbers and copy them to the windows clipboard. You will then need to put your cursor on a cell (the next blank one you would normally copy to and paste them there.

If I was doing this further I'd go the next step and automate the whole process, going from Sheet 2 to the last sheet 57.

I hope the code above will inspire you to learn a little more VBA and finish off the projcet yourself.

[ramdzan]

thanx.. but actually i was trying to pull all the data from the first sheet to all the remaining sheet, for example, copying 1540 from cell C4 in the details sheet, to cell H8 at sheet AYI and then coying 1540 in cell C5 to cell H8 in dmetric sheet,

[MarvinP]

Hi ramdzan,

After re-reading the above I'm very sorry our language gets in the way of understanding the problem. I thought you started with the 56 sheets and needed to make the detail sheet, given all the others. I've spent about 2 hours working on this problem so far. Now you explain you are given (start with the) details sheet and need to make all the others.

Do this. Remove Col F (it is blank) and all blank rows on the rAF (details) sheet. This will make it a list or table. Then do AutoFilters on the sheet. Select a single rAF_Loans_Customer and use this data to build your individual sheet. Do this for each successive customer. This will make you problem much easier.
See http://office.microsoft.com/en-us/ex...010082314.aspx
I hope this helps with your problem.

[jaslake]

Hi ramdzan
I received your PM and wanted to let you know that I've some ideas about this (assuming I understand the issues).

It'll involve a LOT of clean up work on your part to "normalize" the target sheets such that they all "look" the same...that is, fields to be transferred from the detail sheet need to aim for a target cell that's the same on all target sheets. For example, Account Number can't be in cell B19 on some sheets, B24 on other sheets, etc.. It can't be aiming to Column H on some target sheets and Column I on other target sheets. I say it "can't"...it certainly could...but I'd want no part of it.

Additionally, you'll need to "fix" the formatting on all your target sheets, and, with the approach I'll suggest, you'll need some strict discipline in your sheet naming convention.

I'm still in the early stages of developing this but I wanted you to know that I am looking at it.

I'll upload a sample soon.

[MarvinP]

Hey John,

I think he wants to start with the Detail Sheet (Sheet1) and build all 56 other sheets. I kind of decided he should have only one "second sheet" and do a filter from the detail to a second. Somewhere on the detail or the second should be a customer name and that would do a filter of the detail and create his second sheet.

I'm pretty burnt out on this one now that I was working backwards on it before and spent a lot of time. I'd hate for you to go in the wrong direction like I did.

I stopped trying when it looks like there is more information on the customer sheets than the detail. I couldn't decide how to construct this from the detail (sheet1).

[jaslake]

Hi MarvinP

I appreciate your concern and your frustration. Been involved with several of those the last few days where the OP's needs weren't clearly defined or changed dramatically midstream.

IF i have a handle on ramdzan's needs (that's still an open question), I've an approach. I'm currently working on the issue of multiple accounts and how to incorporate the needed lines. I've some ideas and am testing them.

If you're like me, you'll follow this thread...offer your input any time.

[smohyee]

Can I just say: props to everyone in this thread for dedication! I found the OP's request quite overwhelming, especially since the level of work he's looking for is probably worth something significant. But I'm inspired by the amount of effort forum members put into helping a random newbie.

[nitishrpsharma]

Hi there, i am new to vb and looking for the code to import data from two csv files (a1 & a2) stored on my desktop in to sheet name "data" of my workbook named "lapu". This is my first post. Thanks in anticipation

[jaslake]

Hi nitishrpsharma

For your first post, you're off to a bad start. You posted your issue on another OP's thread...not an acceptable practice on this Forum. Please start a new thread with your issue. If another thread is appropriate to your issue, provide a link to such thread.

[jaslake]

Hi smohyee
Appreciate your compliment...let's save the "props" until we have a workable solution for the OP (although...do appreciate the thought)

[jaslake]

Hi ramdzan

I should have something for you to play with tonight or in the AM.

[jaslake]

Hi ramdzan

I'm getting caught up in my knickers with this. I need you to look at this and let me know what's working and what's not working.

I'm attaching a workbook that includes 8 workbooks from you're original file (57 or so was more than I wanted to deal with).

You'll notice I made some changes to the naming of the included worksheets. For example:

XAL Learng was changed to XAL Learning
b&j was changed to b & j

These changes were made to conform with the rAF_Loans_Customer names in rAF (details) worksheet.

The procedure builds a file (worksheet) of file names included in the workbook. It then looks for and filters rAF (details) worksheet based on those file names. So, you need to check your worksheet names and see how they compare to the actual customer names in the detail worksheet. Take a look at the file names...you'll see that the file name is PART OF THE CUSTOMER NAME...not the entire name...but PART OF IT.

The good thing is, the procedure will crap out if the worksheet doesn't exist..so, you'll know you have a naming issue. I can put an error trap into the procedure to prevent this...but I want you to focus on this issue and prevent it up front...then I'll provide the error trap.

The procedure can be run multiple times...it rebuilds the individual worksheets each time. Formatting may be an issue...not my primary concern at the moment...my primary concern is...DOES IT APPROACH to doing what you wish it to do.

Run Sub test() from Module 2.

Get back to me with issues.