Macro problem

[Vokes7791]

Hi All,

Can anyone help me here?

I have the below code, the macro enable the 'cursor' to return to D4 but would like the code to also send the 'cursor' to Sheet1 cell B7 if B6 has text in. Is this possible?


Private Sub CommandButton1_Click()
With Range("D4:D14")
.Copy
Sheets("Sheet1").Range("B6:k6").PasteSpecial Paste:=xlPasteAll, Transpose:=True
.ClearContents
Range("d4").Select
End With
End Sub

 

[JackDanIce]

Try

Private Sub CommandButton1_Click()

With Range("D4:D14")
  .Copy
  Sheets("Sheet1").Range("B6").PasteSpecial Paste:=xlPasteAll, Transpose:=True
  .ClearContents
End With
Range("D4").Select

If Not IsEmpty(Range("B6")) Then
   With Sheets("Sheet1")
      .Select
      .Range("B7").Select
  End With
End If

End Sub

 

[Vokes7791]

Hi JackDanIce,

Thanks for code, but not working, Cursor won't go to B7 still.

 

[JackDanIce]

Is B6 truly empty or does it contain non-visible characters (e.g. spaces). Instead, try:

Private Sub CommandButton1_Click()

With Range("D4:D14")
  .Copy
  Sheets("Sheet1").Range("B6").PasteSpecial Paste:=xlPasteAll, Transpose:=True
  .ClearContents
End With
Range("D4").Select

If Len(ActiveSheet.Range("B6")) < 1 Then
  With Sheets("Sheet1")
    .Select
    .Range("B7").Select
  End With
End If

End Sub

 

[Vokes7791]

Works a treat! thanks for your help JackDanIce.

Vokes7791

 

[JackDanIce]

Glad it does and you're welcome