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]

Need to prepare some coffee to stay awake. Will be back in a jiffy.

 

[RAJESH1960]

If x > 1 Then Sheets("ImportMasters").Range("A2:" & LastColumnLetterSheetImportMasters & x + 1).FillDown                  ' Create range needed to copy

What if x is not greater than 1..?

 

[johnnyL]

If it is not > 1 then your one row of formulas is left in tact.

 

[RAJESH1960]

If it is not > 1 then your one row of formulas is left in tact.

Ok. But why is the row deleted that we need to find out.

 

[RAJESH1960]

Sheets("ImportMasters").Range("A2").Resize(x, LastColumnNumberInRow).Copy

Can this be the problem..? Not sure. Because this line appears after the above line

 

[johnnyL]

If x > 1 Then Sheets("ImportMasters").Range("A2:" & LastColumnLetterSheetImportMasters & x + 1).FillDown                  ' Create range needed to copy

What if x is not greater than 1..?

That fixes the issue for the formulas. I am not sure what problem you are wanting to address.

 

[RAJESH1960]

That fixes the issue for the formulas. I am not sure what problem you are wanting to address.

When there is one ledger to generate xml, the code erases the Import Master row2. That is the problem.
I checked many times when there is more than 1 ledger to generate it works perfectly.

 

[johnnyL]

The .FillDown has nothing to fill down if x is not greater than 1.

 

[RAJESH1960]

The .FillDown has nothing to fill down if x is not greater than 1.

Hahahaha... I understood that. But the issue still persists.

 

[johnnyL]

    x = Sheets("MasterData").Range("B2:B" & Sheets("MasterData").Range("B" & Rows.Count).End(xlUp).Row).Rows.Count  ' Get count of rows to write to file

x = number of ledgers ... only 1 ledger means x = 1

The check for 'If x > 1 then ...' is the check for number of ledgers.