Trevor3007
Well-known Member
- Joined
- Jan 26, 2017
- Messages
- 675
- Office Version
- 365
- Platform
- Windows
In the 'red'code below , if the user chooses N, I need it to go to the 'green' code bit at the bottom just before the 'exit' so the cursor ends up in cell A2 & a msgbox dispalys to prompt the user with a message of advise. It works fine if Y is chosen?
Sub importdata()
'
' importdata Macro
''
Workbooks.Open Filename:="C:\Users\xyz\desktop\info\coffe_data.xlsx"
Range("A2:H3007").Select
Selection.Copy
Windows("test_lookup.xlsm").Activate
Range("A2").Select
ActiveSheet.Paste
Windows("lookup_data.xlsx").Activate
Application.CutCopyMode = False
ActiveWindow.Close
If MsgBox("Do You Want A Border Placed Around This Worksheet", vbYesNo + vbQuestion) = vbNo Then Exit Sub
Range("A1:N3007").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("A2").Select
Range("I1:I3007").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A2").Select
MsgBox "End Of Macro,Please Save The File To An Area Of your Choice If You Are Finished or Rerun Again."
End Sub
Any suggs?
KR
Trevor3007
Sub importdata()
'
' importdata Macro
''
Workbooks.Open Filename:="C:\Users\xyz\desktop\info\coffe_data.xlsx"
Range("A2:H3007").Select
Selection.Copy
Windows("test_lookup.xlsm").Activate
Range("A2").Select
ActiveSheet.Paste
Windows("lookup_data.xlsx").Activate
Application.CutCopyMode = False
ActiveWindow.Close
If MsgBox("Do You Want A Border Placed Around This Worksheet", vbYesNo + vbQuestion) = vbNo Then Exit Sub
Range("A1:N3007").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("A2").Select
Range("I1:I3007").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A2").Select
MsgBox "End Of Macro,Please Save The File To An Area Of your Choice If You Are Finished or Rerun Again."
End Sub
Any suggs?
KR
Trevor3007