CODEWITHSUNDEEP
excelvba
John22
John22

Reputation: 61

how to copy and paste between 2 different sheets and columns

I would like to be able to copy the selected row from sheet 3 range B: G and paste the cells to sheet 4 column A: F but when the operation ends I find the formatting in the A: F range and the pasted data in the B: G range Thanks

Sub Elimina_selezione()
Worksheets(3).Activate
ActiveSheet.Unprotect
Call copia_archivio
Worksheets(3).Activate
ActiveCell.EntireRow.Delete
Sheets(3).Protect
End Sub

Sub copia_archivio()        
Dim i As Range
Dim rig As Long
Sheets(3).Select
ActiveCell.EntireRow.Copy
Worksheets(4).Activate
ActiveSheet.Unprotect
With Sheets(4).Range("A" & Rows.Count).End(xlUp).Offset(1)
    .PasteSpecial Paste:=xlPasteValues, Transpose:=False
    With Intersect(.EntireRow, .Parent.Columns("A:F"))
        .Interior.ColorIndex = 44
        .Borders.LineStyle = XlLineStyle.xlContinuous
    End With
End With
        
End Sub

Upvotes: 0

Views: 73

Answers (2)

DimRasRecordset
DimRasRecordset

Reputation: 66

Better to use range and worksheet variables where possible. The only activation necessary is to get the selection on sheet3, I think. (tested code)

Sub CopyRowFromSheet3to4andDeleteRow()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim wsSrc As Worksheet: Set wsSrc = wb.Worksheets("Sheet3")
    Dim wsTgt As Worksheet: Set wsTgt = wb.Worksheets("Sheet4")
    wsSrc.Activate 'need to activate it to get its selected range
    Dim rSel As Range: Set rSel = wb.Windows(1).Selection 'window(1) is always the active worksheet
    Dim iSelRow As Long: iSelRow = rSel.Row
    Dim rSrc As Range: Set rSrc = wsSrc.Range("B" & iSelRow & ":G" & iSelRow)
    Dim iTgtRow As Long: iTgtRow = wsTgt.Range("A" & wsTgt.Rows.Count).End(xlUp).Row + 1
    Dim rTgt As Range: Set rTgt = wsTgt.Range("A" & iTgtRow & ":F" & iTgtRow)
    rSrc.Copy rTgt
    rTgt.Interior.ColorIndex = 44
    rTgt.Borders.LineStyle = XlLineStyle.xlContinuous
    Dim rDelSrcRow As Range: Set rDelSrcRow = wsSrc.Range(iSelRow & ":" & iSelRow)
    rDelSrcRow.Delete xlShiftUp
End Sub

Upvotes: 1

Ozgun Senyuva
Ozgun Senyuva

Reputation: 466

You are saying that you copy the selected row from sheet 3 range B: G. As far as I understand you are trying to copy not the whole row but only the range of intersection with columns b:g. But your code copies the whole row starting from column A, not B.
You should re-code the range you want to copy and replace "activecell.entirerow" with it.

Upvotes: 0

Related Questions