shah
11-17-2016, 08:25 AM
Hi,
I have been working on a project and needed to include VBA codes to close the original file, copy it and save it in a different location with a specific naming format. I have achieved this but now need to insert more codes to firstly identify blank rows in a given range and then delete them (there are 3 different ranges I want to consider being cells B18 to B25, B27 to B31 and B33 to B38). I also need to include an image based on what text is in cell C7 (which is in a form of a dropdown). I have been trying many different things over the last week but haven't been successful. Please if someone can help me in this i would be really greatful. Thank you in advance! So far, the code I have is the following:
Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="Password1"
ActiveSheet.Copy
Dim SaveName As String
SaveName = ActiveSheet.Range("C5").Text
ActiveSheet.Protect Password:="OperationHUMP"
With ActiveWorkbook
.Worksheets("Media Plan").CommandButton1.Visible = False
.SaveAs "S:\" & SaveName & ".xls"
End With
Workbooks.Open ("S:\" & SaveName & ".xls")
Workbooks("Media Plan.xlsb").Close False
End Sub
I have been working on a project and needed to include VBA codes to close the original file, copy it and save it in a different location with a specific naming format. I have achieved this but now need to insert more codes to firstly identify blank rows in a given range and then delete them (there are 3 different ranges I want to consider being cells B18 to B25, B27 to B31 and B33 to B38). I also need to include an image based on what text is in cell C7 (which is in a form of a dropdown). I have been trying many different things over the last week but haven't been successful. Please if someone can help me in this i would be really greatful. Thank you in advance! So far, the code I have is the following:
Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="Password1"
ActiveSheet.Copy
Dim SaveName As String
SaveName = ActiveSheet.Range("C5").Text
ActiveSheet.Protect Password:="OperationHUMP"
With ActiveWorkbook
.Worksheets("Media Plan").CommandButton1.Visible = False
.SaveAs "S:\" & SaveName & ".xls"
End With
Workbooks.Open ("S:\" & SaveName & ".xls")
Workbooks("Media Plan.xlsb").Close False
End Sub