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

 

[johnnyL]

You get that error every time? If not please explain when you do get the error.

 

[RAJESH1960]

It is not even copying the one ledger NA in Master data sheet.

 

[RAJESH1960]

You get that error every time? If not please explain when you do get the error.

I get the error when one ledger is a mismatch that is NA

 

[RAJESH1960]

The code generated a single ledger in the Master Data sheet. But the import data sheet row 2 got deleted with formulas.

 

[RAJESH1960]

Error.mp4
check this

 

[RAJESH1960]

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

You were right. I missed to remove the duplicates. The error is not in this line. But the Import Masters data is deleted. You can copy it from the hidden sheet again to check

 

[RAJESH1960]

JohnnyL. That is the only issue left. It is time I hit the sack. See you tomorrow. I am sure you will solve the problem.?

 

[johnnyL]

Just to be clear, You have issue with the 'ImportMasters' sheet when you only have one item in the 'MasterData' sheet, no items, or a '#N/A'?

 

[RAJESH1960]

Yes. The only issue is with the Import Master sheet. The data is getting deleted if there is one ledger only. If there is more than one ledger in Master data sheet the code runs perfectly.

 

[RAJESH1960]

When there is one ledger NA then that ledger is displayed in Master Data sheet. That is good, Only Import Master sheet data should not get deleted. While the code resize that one ledger I think it is getting deleted.