Copying columns from one workbook to another

Hello - I think this is one those days when I realize there people with mad VBA skills that I need to use for my research project. I am stuck and despite recording a macro I cannot get to do all sheets (A range) into master.
Two files: FileA and FileB

Sub CopyPasteRange()
Workbooks("FileA.xlsx").Sheets("John B").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("John B").Range("S:S")
Workbooks("FileA.xlsx").Sheets("John B").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("John B").Range("T:T")
Workbooks("FileA.xlsx").Sheets("John B").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("John B").Range("U:U")
Workbooks("FileA.xlsx").Sheets("John B").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("John B").Range("V:V")
Workbooks("FileA.xlsx").Sheets("Mark L").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("Mark L").Range("S:S")
Workbooks("FileA.xlsx").Sheets("Mark L").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("Mark L").Range("T:T")
Workbooks("FileA.xlsx").Sheets("Mark L").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("Mark L").Range("U:U")
Workbooks("FileA.xlsx").Sheets("Mark L").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("Mark L").Range("V:V")
Workbooks("FileA.xlsx").Sheets("Suzanne A").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("Suzanne A").Range("S:S")
Workbooks("FileA.xlsx").Sheets("Suzanne A").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("Suzanne A").Range("T:T")
Workbooks("FileA.xlsx").Sheets("Suzanne A").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("Suzanne A").Range("U:U")
Workbooks("FileA.xlsx").Sheets("Suzanne A").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("Suzanne A").Range("V:V")
Workbooks("FileA.xlsx").Sheets("Eva D").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("Eva D").Range("S:S")
Workbooks("FileA.xlsx").Sheets("Eva D").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("Eva D").Range("T:T")
Workbooks("FileA.xlsx").Sheets("Eva D").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("Eva D").Range("U:U")
Workbooks("FileA.xlsx").Sheets("Eva D").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("Eva D").Range("V:V")
Workbooks("FileA.xlsx").Sheets("Bello").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("Bello").Range("S:S")
Workbooks("FileA.xlsx").Sheets("Bello").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("Bello").Range("T:T")
Workbooks("FileA.xlsx").Sheets("Bello").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("Bello").Range("U:U")
Workbooks("FileA.xlsx").Sheets("Bello").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("Bello").Range("V:V")
Workbooks("FileA.xlsx").Sheets("Beatrice").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("Beatrice").Range("S:S")
Workbooks("FileA.xlsx").Sheets("Beatrice").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("Beatrice").Range("T:T")
Workbooks("FileA.xlsx").Sheets("Beatrice").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("Beatrice").Range("U:U")
Workbooks("FileA.xlsx").Sheets("Beatrice").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("Beatrice").Range("V:V")
Workbooks("FileA.xlsx").Sheets("Levi").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("Levi").Range("S:S")
Workbooks("FileA.xlsx").Sheets("Levi").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("Levi").Range("T:T")
Workbooks("FileA.xlsx").Sheets("Levi").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("Levi").Range("U:U")
Workbooks("FileA.xlsx").Sheets("Levi").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("Levi").Range("V:V")
Workbooks("FileA.xlsx").Sheets("Riverforte").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("Riverforte").Range("S:S")
Workbooks("FileA.xlsx").Sheets("Riverforte").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("Riverforte").Range("T:T")
Workbooks("FileA.xlsx").Sheets("Riverforte").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("Riverforte").Range("U:U")
Workbooks("FileA.xlsx").Sheets("Riverforte").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("Riverforte").Range("V:V")
Workbooks("FileA.xlsx").Sheets("Clif").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("Clif").Range("S:S")
Workbooks("FileA.xlsx").Sheets("Clif").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("Clif").Range("T:T")
Workbooks("FileA.xlsx").Sheets("Clif").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("Clif").Range("U:U")
Workbooks("FileA.xlsx").Sheets("Clif").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("Clif").Range("V:V")
Workbooks("FileA.xlsx").Sheets("Lee S").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("Lee S").Range("S:S")
Workbooks("FileA.xlsx").Sheets("Lee S").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("Lee S").Range("T:T")
Workbooks("FileA.xlsx").Sheets("Lee S").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("Lee S").Range("U:U")
Workbooks("FileA.xlsx").Sheets("Lee S").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("Lee S").Range("V:V")
Workbooks("FileA.xlsx").Sheets("John S").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("John S").Range("S:S")
Workbooks("FileA.xlsx").Sheets("John S").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("John S").Range("T:T")
Workbooks("FileA.xlsx").Sheets("John S").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("John S").Range("U:U")
Workbooks("FileA.xlsx").Sheets("John S").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("John S").Range("V:V")
Workbooks("FileA.xlsx").Sheets("Iain").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("Iain").Range("S:S")
Workbooks("FileA.xlsx").Sheets("Iain").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("Iain").Range("T:T")
Workbooks("FileA.xlsx").Sheets("Iain").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("Iain").Range("U:U")
Workbooks("FileA.xlsx").Sheets("Iain").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("Iain").Range("V:V")
Workbooks("FileA.xlsx").Sheets("Vic P").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("Vic P").Range("S:S")
Workbooks("FileA.xlsx").Sheets("Vic P").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("Vic P").Range("T:T")
Workbooks("FileA.xlsx").Sheets("Vic P").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("Vic P").Range("U:U")
Workbooks("FileA.xlsx").Sheets("Vic P").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("Vic P").Range("V:V")
Workbooks("FileA.xlsx").Sheets("India").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("India").Range("S:S")
Workbooks("FileA.xlsx").Sheets("India").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("India").Range("T:T")
Workbooks("FileA.xlsx").Sheets("India").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("India").Range("U:U")
Workbooks("FileA.xlsx").Sheets("India").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("India").Range("V:V")
Workbooks("FileA.xlsx").Sheets("Singh").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("Singh").Range("S:S")
Workbooks("FileA.xlsx").Sheets("Singh").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("Singh").Range("T:T")
Workbooks("FileA.xlsx").Sheets("Singh").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("Singh").Range("U:U")
Workbooks("FileA.xlsx").Sheets("Singh").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("Singh").Range("V:V")
Workbooks("FileA.xlsx").Sheets("Pranav").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("Pranav").Range("S:S")
Workbooks("FileA.xlsx").Sheets("Pranav").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("Pranav").Range("T:T")
Workbooks("FileA.xlsx").Sheets("Pranav").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("Pranav").Range("U:U")
Workbooks("FileA.xlsx").Sheets("Pranav").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("Pranav").Range("V:V")
Workbooks("FileA.xlsx").Sheets("Ling").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("Ling").Range("S:S")
Workbooks("FileA.xlsx").Sheets("Ling").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("Ling").Range("T:T")
Workbooks("FileA.xlsx").Sheets("Ling").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("Ling").Range("U:U")
Workbooks("FileA.xlsx").Sheets("Ling").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("Ling").Range("V:V")
Workbooks("FileA.xlsx").Sheets("Fungo").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("Fungo").Range("S:S")
Workbooks("FileA.xlsx").Sheets("Fungo").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("Fungo").Range("T:T")
Workbooks("FileA.xlsx").Sheets("Fungo").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("Fungo").Range("U:U")
Workbooks("FileA.xlsx").Sheets("Fungo").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("Fungo").Range("V:V")
Workbooks("FileA.xlsx").Sheets("Engel").Range("H:H").Copy Workbooks("FIleB.xls").Sheets("Engel").Range("S:S")
Workbooks("FileA.xlsx").Sheets("Engel").Range("I:I").Copy Workbooks("FIleB.xls").Sheets("Engel").Range("T:T")
Workbooks("FileA.xlsx").Sheets("Engel").Range("J:J").Copy Workbooks("FIleB.xls").Sheets("Engel").Range("U:U")
Workbooks("FileA.xlsx").Sheets("Engel").Range("K:K").Copy Workbooks("FIleB.xls").Sheets("Engel").Range("V:V")

End Sub


I want to combine two workbooks into one by copying Round 2 survey results from "FileA" into the master workbook FileB. The tabs are named the same (same respondents). I just want to copy the columns that are part of Round 2 plus the comments in FileA after the Comments section.

The reference terms are consistent.Is it possible to create a macro that will copy all sheets Round 3 fileA into FileB?

Thank you so very much for any input/help.

Hi wanian7,
. It is almost certainly possible to copy / consolidate data from 2 sheets into 1 with VBA.
. But It is very unclear exactly what you want.
.
. You seem to have given a File B with reduced sample data, and that is good.
. It is not clear what you are talking about by “Round 2“
. .....
. The one FileA that you have uploaded appears empty?
.
. You need to supply two FileA’s, a Before and an After
.
. The Before should look just as it does before running of any macro.
.
. The After should be hand filled by you so that it looks exactly as you want it to After running of any macro that we do for you, based on the actual sample data you supply in FileB.
.
. It is most likely then that any code we do for you would then also work on much bigger files with more data and more sheets .

. Alan

Dear Alan,
File B is the master file where we are compiling results of everyone's findings individually and providing qualitative points.
File A is compilation of everyone's results for Round 3. I need to bring in all Round 3 results into workbook FileB. Problem is every student mixed the questions up and if I do a blind column copy without matching it with the question number also called "SPII" then they will be mixed in the copying and pasting.

Basically each student has a spreadsheet in Master FileB and FileA is Round 3 with High Medium and Low columns under it.
An Example is John B where his results for Round 3 have been matched from FileA to FileB. I already have the headers in each persons tab. I have uploaded sample files the replicate the scenario (Not sure if it can scripted with the copying and pasting starting under the ranking and matching to "SPII" for the right question.

I hope these helps and thank so much for your comments and for reviewing post with helpful feedback.

Thank you.

Hi wanian7,

. Ok, the info from Post #3 makes it much clearer, Thanks
.
. Based on the example you gave, i think it will be easy to write a code to reproduce that.
.
. I will take a look at it later for you


. Some Questions:

. 1) Are the tabs always in the same order in both files? Or can this vary?

. 2a) Are there always 8 questions ( as determined by column F ) in File A ? Or can this vary?
. 2b) If the number can vary, would it be the same for all persons for the same Round ?
, 2c) Would the order of the questions ( as determined by "SPII" ( Column F FileA ) ) be the same for all persons in a Round? Or can that vary?

. 3) I do not quite understand what you mean here?

Originally Posted by wanian7

...... (Not sure if it can scripted with the copying and pasting starting under the ranking ........

... can you explain that again please?

. 4) I am not sure what the significance of The Points in FileB is ?

Using Excel 2007

Row\Col	W	X	Y
1
2
3		Points
4
5

Beatrice

... is there any significane that I should know about that?

Alan

Hi,

. OK, I have an initial code for you:


Some initial remarks, assumption:
.
. 1) I assume that file A is a typical “Round” File. ( I have renamed it in my Folder as “Round3” and refer to it from now as File “Round3” )
. 1a) I assume that following files ( Round4 , Round5 ... etc.. ) will always have similar formats to “Round3”. I will refer to thes files as “Round3” or Round File or Round Files

. 2) I will refer to File B from now on as the “FileBMaster” or “MasterB” Or Master.
. 2a) I will assume for all persons the unique question reference numbers in column I ( “SPII” ) are identical for all persons in the FileBMaster
. 2b) I will assume this list may be added to at a later date, that is to say the reference numbers could extend further down.
. 2c) I assume the general characteristics of all sheets in Master are the same (Only differing in the practice by the data in the 4 columns ( High, Medium, Low, Comments )

.3 ) I will assume that further “Rounds” are added in FileBMaster alongside and touching ( to the right) of the last Round Entries, that is to say the new round data is offset form the last round data by 4 columns, and zero rows, that is to say starting on the same row.
.3a) I note that you have already included headings for round 3, but I will assume the start point is thus: ( for all sheets in Master )

Using Excel 2007

Row\Col	O	P	Q	R	S	T	U	V
2
3	Round 2 Quantitative Ranking			Comments
4	High	Medium	Low
5	0	1	0
6	1	0	0
7	0	1	0	Asked
8	1	0	0
9	0	0	1	Cocomet
10	1	0	0
11	0	0	1	ML
12	0	0	1

John B

. Note heading “Comment” should be in row 3.

. 4) I assume that for Round files the new data always starts in cell H5 ( 5 , 8 ) , and that the question reference numbers start in cell F5 ( 5 , 6 )
. 4b) I assume the number of rows of data will be equal to that of the rows in the unique question reference numbers column I ( “SPII” ) in Master

. 5) In abscence of your answerers to my questions I assume a few other things for now such as that sheets are in exactly the same order in both Files
.
............................

.. So, anyways I have a first shot of a code that seems to work. For example taking the start Point as that spreadsheet screen shot I gave above, and running the code with , for example, your test data for the Round Sheet of..

Using Excel 2007

Row\Col	F	G	H	I	J	K
2
3			Round 3 Quantitative Ranking			Comments
4	SPII	V	High	Medium	Low
5	402F		0	0	1
6	403A		0	0	1
7	405A		0	1	0	WAHR
8	405C		1	0	0
9	405P		1	0	0
10	406B		0	1	0
11	407D		1	0	0
12	408Q		0	0	1
13
14

John B

....
I get this in the first sheet:

Using Excel 2007

Row\Col	S	T	U	V
2
3	Round 3 Quantitative Ranking			Comments
4	High	Medium	Low
5	0	1	0	WAHR
6	1	0	0
7	0	1	0
8	1	0	0
9	0	0	1
10	1	0	0
11	0	0	1
12	0	0	1
13

John B

... That result ties up with what you gave as sample output. I also ran the code for the entire file and it appears at first glance to work correctly and give the correct results.
. see how you get on with it, and we will maybe discuss any problems or changes later..

Alan


Code in next Thread Post # 6

Code for post # 5

Please Login or Register  to view this content.

Hi Ian,

Originally Posted by wanian7

Hi Alan,
Sorry I got tardy in response to the questions.
1. I have put the order of tabs(sheets) in round three to be the same as the master file, but I know they were not the same order when they were compiled by one of the other teaching students.
... the code could be adapted to allow for the different order as long as the spelling of the names is always the same, or at least for the first few characters in the name
2a. The questions can vary, we tried to have the students have a minimum of 8 questions but each question is weighted differently in the qualitative review. I think for the purpose of this each question has a unique ID and will remain the same for each student from round 3.
2b.The number can vary- but should be the same for each student.
2c. The order of questions may vary, but are the same for each person. Example a question may be in row 15 for the student file and row 13 in the master depending on how each student approached the survey.
.. the code allows for that and reorders the rows accordingly before pasting into the Master
3. I meant to ask or suggest that since I have headers of ranking - High, Medium and Low can the macro be able to see that and paste the results in the right cell under the right rank... Just an idea.
- the code already does that – I tested it several times and it continually adds data to the right exactly as I think you want
4. Points no significance just that we usually have a comments and points column after the round results.
- I deleted that bit, that is to say the code overwrites that as it pastes in the new info - the code can easilly bee modified so as not to remove that - It would than enter to the right of anything else, (regardless of what row if, if any , thers is any info in. - It would simply paste in at the next fully free column. So the offset to the right could vary each time depending on exactly what extra info is to the right of the last inserted data)

......

. Try the code and See how you get on. Then let me know of any problems or modifications you wish, and i will take another look at it later..

Alan

Hi wanian7,

. 1 ) Pleas use code Tags # when posting at ExcelForum - ( See notes in my signature ) -

Please Login or Register  to view this content.

..........................................................................

. 2 ) I am struggling to understand any of your comments:

. 2a )

Originally Posted by wanian7

......
“THIS I SUSPECT TO BE MY PROBLEM...My first counting row with the Round 1, Round 2 data is 24 so I changed it to Row 24” .........

.... You have never had anything in any File you gave me in row 24. So I have no idea what you are talking about there...

..............

. 2 b )
. I have no idea what you are talking about here.

Originally Posted by wanian7

..... I changed to 26,2 because my question ID is in cell 26.2 column 2 so LuQr, 2) .........

uQMr() becomes an Array full of the question reference number as you gave them in the correct order in the Master File B

Using Excel 2007

Row\Col	I
5	405A
6	405C
7	406B
8	407D
9	408Q
10	405P
11	403A
12	402F

John B

.........................................

. 3 )
. 3 a )

Originally Posted by wanian7

...... Is there away to use column headers as the lead in to start the copy and pasting? Example row P25 has the Comments and M24 has a merged header "Round 2" that extends to P24. Is it possible to make it copy Round 3 in a selected range? of the Master spreadsheet? .......

. Sorry, but again I have no idea what you are talking about. You are talking again about rows 24 and 25 which have never had any data in in any of the sample files you sent me. It is possible to paste anywhere you want , but you must tell, or rather show, me what you want. I cannot guess ****
.............................................

. 3 b)

Originally Posted by wanian7

.......... Is it possible to make it copy Round 3 ......which ensuring the question ID are aligned?.....

The code I gave you copies as you asked AND reorganises the data and so pastes it in ensuring that the question ID are aligned.

...........................................................

. 4 )

. The modified code you sent to me does nothing, as the lines shown in red below give me a compiling error. So I have no idea exactly how you modified my code:

Please Login or Register  to view this content.

.............................................

.. ****I suggest you try again to send me 2 versions of FileB Master. And send again File A, possibly with some more representable data.
. IMPORTANT:
For the two FileB Master Files
. – 1 should be the Before... It should look like it does before the macro is run to take in Round 3 results.
. – The other should look exactly as you want it after running of the macro, based on the sample data in File A that you give me

Hi,

Originally Posted by wanian7

......I wish the macro asked for a "From" and "To" range, .......

.. 1 ). So do you want the code to ask , for example, for the top left cell in which the new Round data is in File A..`
. Or
would that always be cell F 24


. 2) Similarly, do you want the code to ask for the top left cell of where you want the reordered results to come into File B. ?
Or
do you always want the latest Round results alongside the last Round results, ( as appears currently to be the case )?


Alan

War and Peace .. next Chapter... wa___ng with wanian..

Hi wan_ian,

.. OK next try...

. 1 ) I assume for all tabs ( names ) in File B the order of SPII question reference numbers remains the same and are to be found in column B starting at row 26. It may be added to at some time in the future – The code will always look for and find the last entry down in column B. ( The contents of this column are held in a 2 dimensional 1 column Array uQMr() . The code will arbitrarily always reference the range from the first sheet, That is to the say the first tab looking from the left, regardless of its name

. 2 ) Similarly I assume in File A the SPII question reference numbers are always to be found in column B starting at row 26, ( But the order can be different in ( jumbled up ) and may vary for different names. ) ) . I have modified the code over earlier versions to allow for the possibility of a different number of SPII question reference numbers in different tabs ( names ) in File A

. 3 ) I assume that the number of the SPII question reference numbers in File A is less than or equal to, but not more than, the SPII question reference numbers in File B
. 3 a ) ( If the number of the SPII question reference numbers in File A is less than the SPII question reference numbers in File B, then the extra rows in the SPII question reference numbers in File B are not ones that can be found in File A SPII question reference numbers – this point needs a bit of thinking about.... )

....................
. Anyways I did a new code for you....
. I did my head in a bit , after I thought I was getting strange results from the code...
. But then I sussed it out
. 1 ) Your “After” sheet was wrong ( I think )
. 2 ) In your File B I think someone played a nasty trick on you or me... In the range where the Output should be pasted the background color and the text color were both Yellow – Neat trick.. when my code posted .Values in I thought nothing was coming in!!!!

.. coming to point . 1)

Please examine your File A :

Using Excel 2007

This	From	Ians	Before	FileA
FileA	FileA	FileA	FileA	FileA
402F	0	0	1
403A	0	0	1
405C			0
405P			0
406A	0		0
407D	1	0	0	Not sure
408Q	0	0
405W	0	0	1
405X	0	1	0
405A	0	0	1	Food
405Q	0	1	0
405F	0	0	1
406B	0	1	0	WAHR

John B

....
Now look at your File B After.. ( I think Bad results )

Using Excel 2007

This	From	Ians	After	FileB
FileB	FileB	FileB	FileB	FileB
405A	0	0	1
405C	0	0	1
406B	0	1	0	WAHR
407D			0
408Q			0
405P	0		0
403A	1	0	0	Not sure
402F	0	0
405W	0	0	1
405X	0	1	0
405A	0	0	1	Food
405Q	0	1	0
405F	0	0	1

John B

.....................................
.... now look at the results my current code gives..( I think Good results ) .. ( With the SPII columns just tacked on here for demonstration.. )

Using Excel 2007

FileB	FileA		This	From	Alan's	Code
405A	402F		0	0	1	Food
405C	403A				0
406B	405C		0	1	0	WAHR
407D	405P		1	0	0	Not sure
408Q	406A		0	0
405P	407D				0
403A	408Q		0	0	1
402F	405W		0	0	1
405W	405X		0	0	1
405X	405A		0	1	0
405A	405Q		0	0	1	Food
405Q	405F		0	1	0
405F	406B		0	0	1

John B

.....
. I think if you check carefully through the rows you will see that my code gives the correct order. Your After has a few rows in the wrong place.

......

Current code in the next Post Post 15

Code for last post. Post 14 It includes the two Input Boxes that come up to ask for the Top left corner of your Header ranges.. as requested.
. The code appears to fulfil your current request

Please Login or Register  to view this content.

Results as Pasted in Johny B.. Good Results....

Using Excel 2007

Row\Col	S	T	U	V	W	X
23
24	Comments	Round 3 Quantitative Ranking			Comments	View Category
25		High	Medium	Low
26		0	0	1	Food
27				0
28	Asked	0	1	0	WAHR
29		1	0	0	Not sure
30	Cocomet	0	0
31				0
32	ML	0	0	1
33		0	0	1
34		0	0	1
35		0	1	0
36		0	0	1	Food
37		0	1	0
38		0	0	1

John B

Hi Ian,

Originally Posted by wanian7

Dear Alan - I am getting a break at # Let arrR() = .Range(rngTLR.Offset(2, 0), rngTLR.Offset(UBound(uQRr(), 1) + 1, 3)) 'Unordered Round data for this sheet.

.. congratulations..
.. we can all do with a good break sometimes .

.. But seriously....

. That does not give me much to go on, - what error do you get? What error number?

Alan

Its the sticky - Run-time error '1004' Method 'Range' of object'_worksheet' failed at # Let arrR() = .Range(rngTLR.Offset(2, 0), when I bring the cursor it shows zero as in rngTLR.Offset(2, 0)=0.

After debugging I noticed it did have a results for just the first tab "John B".

I simply ran it with the files uploaded and made one change of the source file to "FileA".

What am I missing - Dr Alan? Thanks for the humor sure made an intense moment light for me.

Additional notes: I unmerged the header cells on row 24 and now getting and error X400!

Ian
OK. I seem to be getting that error as well now in some files.. I confese I may not have worked through every sheet in every file I have from you. In the previous files I went through all sheetes and had no errors.
. I will investigate and get back if I find out what the problem is.
Alan

. Hi Ian,
. I may think I may have supplied you with a pile of wank with that last code. Sorry about that. I had just started learning to use that thing that asks you for the Top left Corner.
. And I got it a bit wrong. Oops...

. I think this file is OK, or certainly better.
. Any more problems, get back , I will take a look tomorrow.
Alan

Better code:-

Please Login or Register  to view this content.

P.s. It would have been nice if you could have replied to my comments in Post #14 and told me whether your After results or the results I showed from running my code were correct!! But understandable as i supplied you with a load of crap!!
P.s.2 – Don’t forget what I said about the yellow text and background color in Post # 14 point .2) either

Hi Ian,

Originally Posted by wanian7

....... I used it to run in the real files, it worked for files have of the files and then got the error "Run time error "13" - Type Mismatch" and highlights at #Let arrM() = Application.Index(arrR(), rws(), clms() -
Almost there- so close to get it .
Cheers!......

....I take it from this bit:

Originally Posted by wanian7

........worked for files have of the files and then got the error "Run time error ........

.. that you mean to say that you are getting an error at line

Let arrM() = Application.Index(arrR(), rws(), clms())..

... It is not clear from what you said exactly when.
.................................
Anyway.
.This line

Let arrM() = Application.Index(arrR(), rws(), clms())

is unfortunately the only part of the code whose workings I do not understand. That is because after some 6 months of asking , I come to the conclusion that no one on this earth can understand how Application.Index works when taking Arrays as its second and Third arguments...
http://www.excelforum.com/excel-new-...ml#post4093924
http://www.excelforum.com/excel-new-...ml#post4093843
http://www.excelforum.com/excel-prog...ml#post4139923
http://www.excelforum.com/excel-prog...ml#post4093653
http://www.excelforum.com/excel-prog...ml#post4088055
http://www.excelforum.com/excel-prog...ml#post4082174
http://www.mrexcel.com/forum/excel-q...l?#post4174322
http://www.excelforum.com/excel-new-...ba-arrays.html

...... So it may be a bit of trial and error to find out what is causing that line to fail!!!

..So

.. 1 ) the best I can offer is that you post the file that is giving you problems. ( If necessary desensitise the data by changing it But make sure if you do that you check that with this modified data you still have the error )

. I will then when I have time try to see if i can find out by experimenting what is going wrong.

.. 2 ) Are you familiar with setting a Watch on variables in the Watch Window, and then stepping through the code with F8 and watching see how variables are filled.?
. -If not , then please read up on this. Basically that is all that I will be doing in order to try and find the problem. if you make the effort to learn how to do this, then you should be able to find the problem yourself. – ( The Error could be some value being taken into variable ( such as an actual error #NV, for example, ) that has an incompatible type in later calculations or comparisons in the program...

Alan

Originally Posted by wanian7

....
Almost there- so close to get it .
Cheers!

Hi Ian,
. Any news?
. How did you get on?
Alan