Run time error 450: Copying non zero values from non adjacent columns to another sheet (non adjacent columns)

[JonathanSass]

Hi all,

I have tried to use the following code for my issue described in the header:

Sub CopyDataToJournal()

Dim erow As Long, lastrow As Long, i As Long

lastrow = Sheets("Sheet1").Cells(Rows.Count, 12).End(xlUp).Row

For i = 4 To lastrow
    If Sheet1.Cells(i, 12) <> "0" Then
    Sheets("Sheet1").Range(Cells(i, 12), Cells(i, 13), Cells(i, 17), Cells(i, 18), Cells(i, 19), Cells(i, 20)).Copy
    Sheets("JOURNAL").Activate
    erow = JOURNAL.Cells(Row.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Sheets("JOURNAL").Range(Cells(erow, 1), Cells(erow, 2), Cells(erow, 6), Cells(erow, 7), Cells(erow, 8), Cells(erow, 9))
    Sheets("Sheet1").Activate
    End If
Next i
Application.CutCopyMode = False
   
End Sub

I am new to Excel VBA.

Thank you very much in advance!!
/Jonathan

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub CopyDataToJournal()

Dim erow As Long, lastrow As Long, i As Long

erow = Sheets("JOURNAL").Cells(Row.Count, 1).End(xlUp).Offset(1, 0).Row

With Sheets("Sheet1")
   lastrow = .Cells(Rows.Count, 12).End(xlUp).Row
   
   For i = 4 To lastrow
      If .Cells(i, 12) <> "0" Then
         .Cells(i, 12).Resize(, 2).Copy Sheets("Journal").Range("A" & erow)
         .Cells(i, 17).Resize(, 4).Copy Sheets("Journal").Range("F" & erow)
         erow = erow + 1
      End If
   Next i
End With
Application.CutCopyMode = False
   
End Sub

 

[JonathanSass]

Thank you for the reply but is doesnt work.

To explain a bit more I want to copy values from columns: 12,13,17,18,19,20 in the "Sheet1" to columns: 1,2,6,7,8,9 in "JOURNAL".

In these column there are some rows which have "0" as values. These I do not want to copy. Also the data has to be pasted as values.

Thank you very much in advance!

 

[Fluff]

but is doesnt work.

In what way?

 

[JonathanSass]

This time is gives me "424: Object required"

 

[Fluff]

On which line?

 

[JonathanSass]

On this one:
erow = Sheets("JOURNAL").Cells(Row.Count, 1).End(xlUp).Offset(1, 0).Row

 

[Fluff]

That should be Rows not Row

 

[JonathanSass]

Thanks! Now I get an error on this line:
.Cells(i, 12).Resize(, 2).Copy Sheets("Journal").Range("A" & erow)
1004: Application defined or Object defined error

 

[Fluff]

Does it copy any data to the Journal sheet?