Need help to correct code

[RAJESH1960]

Hello Code Experts

I am using one of Johnnyl”s code, editing it and using it for another project. I need help in some places. Firstly, I am not able to take the range of values below the particulars column which is column C as the cells are merged. So, I added another code to copy the sheet to the working sheet and used unmerged, shifted the heading “Particulars” from B10 to C10. The range need to be edited in the code.

Secondly, here is the tough part. In the List of ledgers sheet, I want the code to avoid 4 names from the list which are Opening Balance, (as per details), 2171377 which can be different in different scenarios and finally Closing Balance. That way I will get the correct ledgers, in MasterData sheet, that I have to create.

Lastly, In the ImportMasters sheet, I am getting 5 excess rows which are empty. They will generate an error when I import the xml file to the server.
If the working sheet is avoided it would be great.
Test NA Masters.xlsm

 

[RAJESH1960]

 AvoidText = "Opening Balance, (as per details), Closing Balance"
    AvoidText = AvoidText & ", " & DataDictionary(UBound(DataDictionary) - 1, 1)

This has solved one of the most difficult part.? Only if there are NA ledgers in the file.

 

[gravanoc]

I looked at the hidden sheets. I don't know what data PasteData or States Codes were supposed to contain.

 

[RAJESH1960]

I have found one more issue. When there is only one ledger NA in Master data sheet, the code erases the 2nd row in ImportMaster Sheet with the formulas. The code runs correctly only if there are 2 or more NA ledgers in master data sheet. By chance if you test this, I have copied the formulas in another hidden sheet by name of formulas.

 

[RAJESH1960]

I looked at the hidden sheets. I don't know what data PasteData or States Codes were supposed to contain.

Maybe you have opened another link. There are only 3 hidden sheets .

 

[RAJESH1960]

I just noticed something important. Please note in the original sheet the list of all particulars is in column C and they are not merged. So, without copying the data to workings, I think you can select all the values in each row from first to last, in column C without any problem. That way the workings sheet can be avoided.

 

[RAJESH1960]

gtg. Will be back in 3 hours.

 

[gravanoc]

The code you have on the MovingData tab includes references to two worksheets that don't exist. One is called PasteData & the other is State Codes

 

[RAJESH1960]

The code you have on the MovingData tab includes references to two worksheets that don't exist. One is called PasteData & the other is State Codes

That applies to some other project. I am copying and editing the codes from here to fit it in this project. I shared them with you that you will get an idea of which line to use and which not to in this project. Of course after editing it.

 

[RAJESH1960]

gravanoc. Do you still have any doubts ?

 

[RAJESH1960]

Sheets("MasterData").Range("B2").Resize(.Count) = Application.Transpose(.keys)                              ' Display unique values on Sheets("MasterData")

Error at the above line. Run time error 13. Type mismatch.
I changed the list of ledgers by adding the NA list to List of Ledgers. This way all the ledgers are a match. So, the code should display the msgbox as All ledgers Available and stop executing the rest of the code.
Please refer the hidden sheets. All the answers are there. I have used the same code in different projects and they are running perfectly. As the format is different in this project, I am a bit confused.

The post is still pending and unsolved. Please Check post #18 that is where it is reached,
Only one change in this code my query will be solved, I want to edit this

Sheets("MasterData").Range("B2").Resize(.Count) = Application.Transpose(.keys)

If there is one entry then don’t resize, directly generate xml, and if there is more than one entry in master data sheet then resize and then generate xml. Like if the count is 1 then don’t resize. I hope you understand what I mean.