Hi friends,
I got following example template with macro code which is doing following:
Once you click on button "Create LOI" in sheet "Analysis" you will able to choose worker and then new sheet(s) will be created by using appropriate data from Analysis.
As you can see, new sheets are created from default sheet called "LOI 4.0", where are input fields between rows 17 & 155.
My problem is, that if I run macro and newly created sheets are updated by appropriate data I have a huge number of blank lines till row 155.
Could you pls help me update following macro code which ensure that all rows without data in mentioned range will be deleted?
Sub Create_LOI_Sheets()
Dim ag As String
Dim r, i, nr, Lastrow As Integer
Dim exists As Boolean
Lastrow = Sheets("Analysis").Range("C" & Rows.count).End(3).Row
Application.DisplayAlerts = False
Application.ScreenUpdating = False
For r = 8 To Lastrow
exists = False
ag = Sheets("Analysis").Cells(r, "A").Value
If NameSelected(Sheets("Analysis").Range("B" & r)) = True Then
For i = 1 To Worksheets.count
If Worksheets(i).name = ag Then
exists = True
End If
Next i
If Not exists Then
Sheets("LOI 4.0").Copy After:=Sheets(Sheets.count)
With ActiveSheet
.name = ag
.Range("H9").Value = ag
End With
End If
nr = Sheets(ag).Range("M" & Rows.count).End(3)(2).Row
Sheets("Analysis").Range("U" & r).Copy Sheets(ag).Range("C" & nr) 'copy/paste Voucher month
Sheets("Analysis").Range("V" & r).Copy Sheets(ag).Range("D" & nr) 'copy/paste Voucher year
Sheets("Analysis").Range("W" & r).Copy Sheets(ag).Range("E" & nr) 'copy/paste Expected month
Sheets("Analysis").Range("X" & r).Copy Sheets(ag).Range("F" & nr) 'copy/paste Expected year
Sheets("Analysis").Range("S" & r).Copy Sheets(ag).Range("G" & nr) 'copy/paste Local Amount
Sheets("Analysis").Range("AD" & r).Copy Sheets(ag).Range("H" & nr) 'copy/paste Parked By / Doc Type
Sheets("Analysis").Range("T" & r).Copy Sheets(ag).Range("J" & nr) 'copy/paste Items
Sheets("Analysis").Range("AF" & r).Copy Sheets(ag).Range("K" & nr) 'copy/paste Default Docu type
Sheets("Analysis").Range("AE" & r).Copy Sheets(ag).Range("L" & nr) 'copy/paste CC & Account & Tr. Partner
Sheets("Analysis").Range("H" & r).Copy Sheets(ag).Range("M" & nr) 'copy/paste Document#
Sheets("Analysis").Range("I" & r).Copy Sheets(ag).Range("N" & nr) 'copy/paste Reference
Sheets("Analysis").Range("J" & r).Copy Sheets(ag).Range("O" & nr) 'copy/paste Doc header text
Sheets("Analysis").Range("Z" & r).Copy Sheets(ag).Range("P" & nr) 'copy/paste Description
Sheets("Analysis").Range("AA" & r).Copy Sheets(ag).Range("Q" & nr) 'copy/paste Action Required /Person Responsible
End If
Next r
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Thanks in advance for your help.
Bookmarks