VBA loop only works for first two sheets - how to fix?

[MESTRAAL]

I have been working on this macro for a few days now, and have hit a problem which I haven't been able to solve with my limited VBA knowledge. It works fine for the first two sheets, but after that it seems to be going wrong and not returning the correct ranges from the second workbook (MACRO Customs Invoices) or re-merging the cells in ThisWorkbook, so the end pdf doesn't have the information I want as is formatted all wrong. Please help me, good people! Your help would be hugely appreciated.

Sub VBAexperimentalv6()


Dim invoice As Workbook
Dim invoicews As Worksheet
Dim origlotno As Range
Dim invoiceci As Range
Dim macroci As Range
Dim remlotno As Range
Dim rhci As Range
Set invoice = ThisWorkbook



For Each invoicews In ThisWorkbook.Worksheets
 
invoicews.Activate
 
Set origlotno = Range("D15", Range("D15").End(xlDown))
Set invoiceci = Range("F15", Range("F15").End(xlDown))
 
Set remlotno = invoicews.Range("D15:E15")
Set remlotno = remlotno.Resize(origlotno.Rows.Count)
 remlotno.UnMerge 'unmerges lot number column

 With origlotno
    Workbooks("MACRO Customs Invoices.xlsx").Sheets("Sheet1").Range("A2").Resize(.Rows.Count, .Columns.Count).Value2 = .Value2  'copies lot numbers from invoice to macro in dynamic ranges
End With

   invoicews.Range("D5:K5").UnMerge 'unmerges client name cells

Workbooks("MACRO Customs Invoices.xlsx").Sheets("Sheet1").Range("M3").Value = invoicews.Range("D5").Value ' copies client name to formula sheet

Set macroci = Workbooks("MACRO Customs Invoices.xlsx").Sheets("Sheet1").Range("J2")
Set macroci = macroci.Resize(invoiceci.Rows.Count, invoiceci.Columns.Count)

invoiceci.Value = macroci.Value ' copies customs info into invoice
invoicews.Range("D5:K5").Merge 'merges client name


remlotno.Merge (True) ' merges lot no in variable range


invoicews.Rows("2:2").RowHeight = 60 ' sets address row to correct height

Set rhci = invoicews.Rows("15:15")
Set rhci = rhci.Resize(origlotno.Rows.Count)
rhci.RowHeight = 21 'resizes lot no rows to allow for two lines


Application.PrintCommunication = False
With invoicews.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
End With
Application.PrintCommunication = True
invoicews.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With invoicews.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.393700787401575)
    .RightMargin = Application.InchesToPoints(0.196850393700787)
    .TopMargin = Application.InchesToPoints(0.393700787401575)
    .BottomMargin = Application.InchesToPoints(0.393700787401575)
    .HeaderMargin = Application.InchesToPoints(0.393700787401575)
    .FooterMargin = Application.InchesToPoints(0.393700787401575)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 300
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlPortrait
    .Draft = False
    .PaperSize = xlPaperA4
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 0
    .PrintErrors = xlPrintErrorsDisplayed
    .OddAndEvenPagesHeaderFooter = False
    .DifferentFirstPageHeaderFooter = False
    .ScaleWithDocHeaderFooter = True
    .AlignMarginsHeaderFooter = False
    .EvenPage.LeftHeader.Text = ""
    .EvenPage.CenterHeader.Text = ""
    .EvenPage.RightHeader.Text = ""
    .EvenPage.LeftFooter.Text = ""
    .EvenPage.CenterFooter.Text = ""
    .EvenPage.RightFooter.Text = ""
    .FirstPage.LeftHeader.Text = ""
    .FirstPage.CenterHeader.Text = ""
    .FirstPage.RightHeader.Text = ""
    .FirstPage.LeftFooter.Text = ""
    .FirstPage.CenterFooter.Text = ""
    .FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
invoicews.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\Richard\Documents\Sortcoding\Customs Invoices\" & Workbooks("MACRO Customs 
Invoices.xlsx").Sheets("Sheet1").Range("M2").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False ' saves as pdf using formula generated file name

   Workbooks("MACRO Customs Invoices.xlsx").Sheets("Sheet1").Range("A2:A250").Clear ' clear formula sheet ready for next invoice

   Next invoicews

   End Sub

 

[Fluff]

Cross posted VBA loop only works for first two sheets - how to fix?

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.