pvuv
10-15-2013, 02:41 AM
Hello All,
I work for charity where I am trying to establish a process of consolidating data.
Case study:
1. I have 3 source Excel workbooks (A.xlsx, B.xlsx, C.xlsx) which contains names, tel numbers and amount donated. I have a master file (master.xlsx) which has a button (update), on clicking it copies data from A, B and C and pastes it Sheet 1, Sheet 2, sheet 3 respectively of Master.xlsx
2. Requirement 1: the data should be copied into master.xlsx without actually opening the A,B,C.xlsx since it will accelerate the process. “i.e. open the sheets in background and Copy”. This will also remove the need to initiate any open or save in the source sheets.
3. Requirement 2: since A,B,C .xlxs are large workbooks (>50mb) I am looking to refrain Windows() and .Activate & .Select method, .Copy & .Paste since it will also copy the formats and is a slower method using more memory which means slower speed. It also removes clipboard use (i think its called direct path but i need for a chunk of cells and not individual)
“SPEED IS THE UNDERLYING TARGET”
4. Requirement 3: A,B,C.xlsx have been known to shift in columns where data is added and subtracted constantly. Hence I want to extract data from A,B,C depending on Heading names.
a. Heading in Master.xlsx: first name, last name, Amount
b. Heading A,B,C.xlsx: name, last name, tel no, Code, Amount, (dont want to link code)
I can’t do a simple extract as that will paste Code aswell and if another column is added in middle, the location will move.
I am attaching A,B,C, Master file. It contains the macros that I have till now. .10708
Your help will be greatly appreciated. I am very new to XL VBA and found these techniques on this website.
I work for charity where I am trying to establish a process of consolidating data.
Case study:
1. I have 3 source Excel workbooks (A.xlsx, B.xlsx, C.xlsx) which contains names, tel numbers and amount donated. I have a master file (master.xlsx) which has a button (update), on clicking it copies data from A, B and C and pastes it Sheet 1, Sheet 2, sheet 3 respectively of Master.xlsx
2. Requirement 1: the data should be copied into master.xlsx without actually opening the A,B,C.xlsx since it will accelerate the process. “i.e. open the sheets in background and Copy”. This will also remove the need to initiate any open or save in the source sheets.
3. Requirement 2: since A,B,C .xlxs are large workbooks (>50mb) I am looking to refrain Windows() and .Activate & .Select method, .Copy & .Paste since it will also copy the formats and is a slower method using more memory which means slower speed. It also removes clipboard use (i think its called direct path but i need for a chunk of cells and not individual)
“SPEED IS THE UNDERLYING TARGET”
4. Requirement 3: A,B,C.xlsx have been known to shift in columns where data is added and subtracted constantly. Hence I want to extract data from A,B,C depending on Heading names.
a. Heading in Master.xlsx: first name, last name, Amount
b. Heading A,B,C.xlsx: name, last name, tel no, Code, Amount, (dont want to link code)
I can’t do a simple extract as that will paste Code aswell and if another column is added in middle, the location will move.
I am attaching A,B,C, Master file. It contains the macros that I have till now. .10708
Your help will be greatly appreciated. I am very new to XL VBA and found these techniques on this website.