Dear Friends
I write below formula, but no luck getting Run time error 9, attached workbook for further information, getting error on underlined area
Thanks in advance![]()
Private Sub CommandButton1_Click() Dim rng As Range Dim temp As Variant Dim I As Long Dim a As Long Dim rng_dest As Range Dim LastRow As String Dim wkb As Workbook Dim wks As Worksheet Application.ScreenUpdating = False '******************************************************************************************************* Set wkb = Workbooks.Open("D:\Purchase Order Data\Purchase Order Data.xlsb", Password:="vv2325", WriteResPassword:="vv2325") Set wks = wkb.Sheets("PO Data") LastRow = wks.Range("E1048576").End(xlUp).Row + 1 I = 1 Set rng_dest = wks.Range("E:O") ' Find first empty row in columns D:J on sheet Master Data Do Until WorksheetFunction.CountA(rng_dest.Rows(I)) = 0 I = I + 1 Loop 'Copy range B16:I34 on sheet Purchase Order to Variant array Set rng = Sheets("Purchase Order").Range("B15:L34") For a = 1 To rng.Rows.Count If WorksheetFunction.CountA(rng.Rows(a)) <> 1 Then rng_dest.Rows(I).Value = rng.Rows(a).Value 'Copy Purchase Order number wks.Range("A" & LastRow).Value = Sheets("Purchase Order").Range("I3").Value 'Copy PO Number2 'wks.Range("M" & LastRow).Value = Sheets("Purchase Order").Range("J3").Value 'Copy Date wks.Range("B" & LastRow).Value = Sheets("Purchase Order").Range("I4").Value 'Copy Company name wks.Range("C" & LastRow).Value = Sheets("Purchase Order").Range("C8").Value 'Copy PO Value wks.Range("D" & LastRow).Value = Sheets("Purchase Order").Range("L41").Value I = I + 1 ActiveWorkbook.RefreshAll End If Next a Application.ScreenUpdating = True End Sub
Vaibhav
Bookmarks