Macro Won't Leave Blank Cells

[pezram]

In this formula I would like it to skip blank cells In the "a" column of the sheet I am copying from any suggestions would be appreciated. If there is no data in the A column I want to skip pasting it to the trades sheet.

Sub copypaste()
  Dim lrb As Long
  With Sheets("trades")
    lrb = .Range("a" & Rows.Count).End(3).Row
    Sheets("Journal").Range("a8:n22,T8:T22").Copy
    .Range("a" & lrb + 1).PasteSpecial (xlPasteValues), Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
  End With
End Sub

 

[puru.sve]

Hi Try this ...

Sub copypaste()
  Dim lrb As Long
  With Sheets("trades")
    lrb = .Range("a" & Rows.Count).End(3).Row
    If Evaluate("countA(A8:A22)") > 0 Then
    Sheets("Journal").Range("a8:n22,T8:T22").Copy
    .Range("a" & lrb + 1).PasteSpecial (xlPasteValues), Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    End If
  End With
End Sub

 

[pezram]

Unfortunately doing the same thing sometimes my sheet will have data in some of the columns right of the "A" column I only want to copy it if it has data in the A column. Thanks for the help.

 

[puru.sve]

Hi I guess... You are looking for this ...

Sub copypaste()
  Dim lrb As Long
  With Sheets("trades")
    lrb = .Range("a" & Rows.Count).End(3).Row
    
    For i = 1 To 22
        If Sheets("Journal").Range("a" & i).Value <> "" Then
            AN = Intersect(Sheets("Journal").Range("A:N"), Sheets("Journal").Range(i & ":" & i)).Address
            TT = Intersect(Sheets("Journal").Range("T:T"), Sheets("Journal").Range(i & ":" & i)).Address
            Rng = AN & "," & TT
            Sheets("Journal").Range(Rng).Copy
            .Range("a" & lrb + 1).PasteSpecial xlPasteValues
            lrb = lrb + 1
        End If
    Next
  End With
End Sub

Let me know if its fine otherwise details your requirement...

 

[pezram]

Thanks puru.sve that solved the blank cells but just want it to copy from row A8 to N22 and T8 to T22, thanks for your help new to this macro stuff.

 

[puru.sve]

Welcome ..... make it for i = 8 to 22

 

[pezram]

Thanks again that worked great. I should have seen that myself oh well learning.