VBA - Easy (?) copy operation

[oggy3000]

Hey,
I do have two little problems with a copy operation in VBA.
Here are the Facts:
1. I want to copy the cell contents of cells A2 to F( until last row) from the sheet "Transfer" to the columns D to I in the sheet "Outbound". In the "Outbound" sheet they are supposed to be copied after the last entry already existing in this sheet.
2. In column G of "Transfer" there are quantities (numbers) which I want to to be copied to "Outbound" as well, but into column J. Here comes the tricky thing: The amount is supposed to be negative. The values in "Transfer" are always positive, and I want them to change to negative when they are copied to "Outbound".

My knowledge of VBA is quite limited so I would appreciate your help very much

Cheers
Jan

 

[VoG]

Try

Sub test()
Dim LR As Long, LR2 As Long, i As Long
LR2 = Sheets("Outbound").Range("A" & Rows.Count).End(xlUp).Row
With Sheets("Transfer")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A2:G" & LR).Copy Destination:=Sheets("Outbound").Range("D" & Rows.Count).End(xlUp).Offset(1)
End With
With Sheets("Outbound")
    For i = LR2 + 1 To LR2 + LR - 1
        .Range("J" & i).Value = .Range("J" & i).Value * -1
    Next i
End With
End Sub

 

[oggy3000]

Hey VoG,
the first half of your code works great and as expected. Thanks a lot for that.
Unfortunately the second part does not change the values to negative. I dont know why because from the code part it looks logical and should be working.

Any Idea on that?

Cheers
Jan

 

[VoG]

Try this small change

Sub test()
Dim LR As Long, LR2 As Long, i As Long
LR2 = Sheets("Outbound").Range("J" & Rows.Count).End(xlUp).Row
With Sheets("Transfer")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A2:G" & LR).Copy Destination:=Sheets("Outbound").Range("D" & Rows.Count).End(xlUp).Offset(1)
End With
With Sheets("Outbound")
    For i = LR2 + 1 To LR2 + LR - 1
        .Range("J" & i).Value = .Range("J" & i).Value * -1
    Next i
End With
End Sub

 

[littleiitin]

Sub copyPaste()
Dim rngCopy     As Range
Dim rngCopyG    As Range
Dim rngPaste    As Range
Dim rngPasteJ   As Range
Dim lngLRow     As Long
    With ThisWorkbook.Worksheets("Transfer")
        lngLRow = .Range("A" & Rows.Count).End(xlUp).Row
        Set rngCopy = .Range("A2:G" & lngLRow)
        Set rngCopyG = .Range("G2:G" & lngLRow)
    End With
    
    With ThisWorkbook.Worksheets("Outbound")
        lngLRow = .Range("D" & Rows.Count).End(xlUp).Row
        Set rngPaste = .Range("D" & lngLRow + 1)
        rngCopy.Copy rngPaste
        Set rngPasteJ = .Range("J" & lngLRow + 1 & ":J" & .Range("J" & Rows.Count).End(xlUp).Row)
        With rngPasteJ
            .Value = Evaluate(rngPasteJ.Address & " * -1")
        End With
        
    End With
    
    
End Sub

 

[oggy3000]

Perfect!
It works just the way it is supposed to
Thank you so much VoG.

Just one more short question: Which command would I have to add if the entries are only to be copied with borders but no colors, not bold and stuff like that?

Cheers
Jan

*edit*
thanks to you as well, littleiitin, but I used VoG's version already

 

[VoG]

This won't do borders but try

Sub test()
Dim LR As Long, LR2 As Long, i As Long
LR2 = Sheets("Outbound").Range("J" & Rows.Count).End(xlUp).Row
With Sheets("Transfer")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A2:G" & LR).Copy
    Sheets("Outbound").Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End With
With Sheets("Outbound")
    For i = LR2 + 1 To LR2 + LR - 1
        .Range("J" & i).Value = .Range("J" & i).Value * -1
    Next i
End With
End Sub

 

[oggy3000]

That did the trick. THank you

Cheers
Jan