Hi, I'm struck in VBA code to find number of rows after fetching the data from another workbook and autofill.
Macro placed in separate file Dumhost.xlsm( Macro workbook) to update Dummy format.csv (File1) from Xando.csv work (file2).
Code
Sub Dum()
Dim FName, wb1 As Workbook, wb2 As Workbook
Dim x, y, i As Long, ii As Long, iii As Long, s As String
Dim Lastrow As Long
FName = Application.GetOpenFilename("excel files,*.csv", , "Select Dummy format & Xando file", , True)
If Not IsArray(FName) Then Exit Sub
If UBound(FName) = 2 Then
Set wb1 = Workbooks.Open(FName(1))
'wb1=Dummy format
Set wb2 = Workbooks.Open(FName(2))
'wb2=Xando
Lastrow = wb1.Range("B" & Rows.Count).End(xlUp).Row
Else
MsgBox "Please select DnD format & L3 file", 16, "To Update DND format"
Exit Sub
End If
Application.ScreenUpdating = 0
wb2.Sheets(1).Range("A2:A5000").Copy _
wb1.Sheets(1).Range("E2")
wb2.Sheets(1).Range("B2:B5000").Copy _
wb1.Sheets(1).Range("F2")
wb2.Sheets(1).Range("D2:D5000").Copy _
wb1.Sheets(1).Range("H2")
wb2.Sheets(1).Range("ABV2:ABV5000").Copy _
wb1.Sheets(1).Range("B2")
wb2.Sheets(1).Range("ABU2:ABU5000").Copy _
wb1.Sheets(1).Range("C2")
wb1.Sheets(1).Range("D2").Select
wb1.Sheets(1).Range("D2:D" & Lastrow).FillDown
wb1.Sheets(1).Range("G2").Select
wb1.Sheets(1).Range("G2:G" & Lastrow).FillDown
wb1.Sheets(1).Range("J2").Select
wb1.Sheets(1).Range("J2:J" & Lastrow).FillDown
wb1.Sheets(1).Range("K2").Select
wb1.Sheets(1).Range("K2:K" & Lastrow).FillDown
End Sub
Display More