Dear Friends
I write below formula, but no luck getting Run time error 9, attached workbook for further information, getting error on underlined area
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
Thanks in advance
Vaibhav
Bookmarks