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

 

[JonathanSass]

no it doesn't copy in any data

 

[Fluff]

Ok, how about

Sub CopyDataToJournal()

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

erow = Sheets("JOURNAL").Cells(Rows.Count, 12).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]

Thanks a whole lot it copied all the data and correctly!!

However, as I an copying formulas, is there a way to make it paste the data as values?

 

[Fluff]

How about

      If .Cells(i, 12) <> "0" Then
         Sheets("Journal").Range("A" & erow).Resize(, 2).Value = .Cells(i, 12).Resize(, 2).Value
         Sheets("Journal").Range("F" & erow).Resize(, 4).Value = .Cells(i, 17).Resize(, 4).Value
         erow = erow + 1
      End If

 

[JonathanSass]

You're a grand champion. Thank you very much

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JonathanSass]

Hi again,

I'm doing this exercise for several sheets into 1 journal tab. With the code now it deletes the previous data in the journal tab. It simply needs to be entered after the data already there. Can this be done?

Thanks a lot

 

[Fluff]

That code should not be deleting anything. It should be adding data to the journal sheet after the last row with a value in col L

 

[JonathanSass]

Unfortuantely this is what happens. It is like the three modules I have created right now (for three tabs that each should be copied in to the journal sheet) delete each other when I run the respective module

 

[Fluff]

Can you post the codes that you are using.