Hello guys,
I hope you are all fine today.
I have already completed my vba scripting through which I would generate workbooks based on a standard template.
The format of all sheets with their respective cell references are totally in harmony with my vba code.
I need only to amend my code for the purpose of deleting specific rows..Whenever i have in my source file specifically in column Q a zero lineitem quantity, i would like to remove the whole row of that item.
Please refer to my source file, then have a look on a sample file through which i show you how my final workbook shall be generated.
Here, you can view into my vba code, that is run inside my template file.
What amendments shall be made to remove only the those rows with Lineitem quantity zero?
Sub test()
Dim rg As Range, i As Long, wb As Workbook
Dim vNames As Variant, v As Variant
Set rg = Workbooks("source file.csv").Worksheets("source file").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.DisplayAlerts = False
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 = 19
.Font.Name = "Times New Roman"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
.Range("C2:C1041").Locked = False
.Protect AllowFiltering:=True
.EnableSelection = xlUnlockedCells
End With
wb.SaveAs ThisWorkbook.Path & "\" & v
wb.Close
Next v
Application.ScreenUpdating = True
MsgBox "finished"
Application.DisplayAlerts = True
End Sub
Please find attached the following files:
source data file.
One sample file named #29390 as the number of its order; i show you through it how i desire to recieve my final generated workbook file.
Thanks a lot for helping me through this.
Bookmarks