Hello guys,
I hope you are all fine.
I am a totally vba newbie with little enquiries.
Could i launch my vba script without the need of opening two of my files simultaneously(a macro file holding the code, and a file named "o" where columns are extracted). Since if i don't open both files i would receive a message (error 9: subscript out of range)
Also, in case the workbooks that are generated had already exist, i dont want to receive a message stating to replace it? i prefer to replace it right away with the new one without notice...
I am thankful for any kind amendment to my below code, or any advice.
God bless you
Sub test()
Dim rg As Range, i As Long, wb As Workbook
Dim vNames As Variant, v As Variant
Set rg = Workbooks("o.csv").Worksheets("o").UsedRange
'get unique names
With CreateObject("Scripting.Dictionary")
For i = 2 To rg.Rows.Count
.Item(rg.Cells(i, 1).Value) = Empty
Next i
vNames = .keys
End With
Application.ScreenUpdating = False
For Each v In vNames
ThisWorkbook.Worksheets("Sheet1").Copy
Set wb = ActiveWorkbook
wb.SaveAs v, 51
rg.AutoFilter 1, v
rg.Offset(1).Columns("A").SpecialCells(xlCellTypeVisible).Copy wb.Worksheets(1).Cells(2, 2)
rg.Offset(1).Columns("U").SpecialCells(xlCellTypeVisible).Copy wb.Worksheets(1).Cells(2, 11)
rg.Offset(1).Columns("Q").SpecialCells(xlCellTypeVisible).Copy wb.Worksheets(1).Cells(2, 13)
With Worksheets("Sheet1")
With .Range("B2:N1014")
.Font.Size = 16
.Font.Name = "Times New Roman"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
.Columns("C:C").Locked = False
.Protect
.EnableSelection = xlUnlockedCells
End With
wb.SaveAs ThisWorkbook.Path & "\" & v
wb.Close
Next v
Application.ScreenUpdating = True
MsgBox "Finished process."
End Sub
Bookmarks