CODEWITHSUNDEEP
vbaexcel
jod51
jod51

Reputation: 111

Auto Copy Cells to New Sheet in Excel VBA

I am using VBA to copy values from sheet "FV" to sheet "AX". I am copying three values from sheet "FV" which reside in cells J7, K7, and L7. My code correctly copies and pastes values J7 and K7, but for some reason L7 isn't getting copied. I am not extremely familiar with VBA, so I am having trouble figuring out the root of the issue in my code.

Sub CopyData()
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    Dim cRng As Range
    Dim dCol As Long
    Set sht1 = ThisWorkbook.Sheets("FV")
    Set sht2 = ThisWorkbook.Sheets("AX")
    Set cRng = sht1.Range("J7:L7")
    dCol = sht2.Cells(1, Columns.Count).End(xlToLeft).Column + 1
    sht2.Range(Cells(1, dCol).Address, Cells(1, dCol).Address) = cRng.Value
End Sub

Thank you for any help!

Upvotes: 0

Views: 282

Answers (1)

QHarr
QHarr

Reputation: 84465

I would suggest lose the .Address as is not required and ensure ranges being set equal to each other are same dimensions e.g

sht2.Range(sht2.Cells(1, dCol), sht2.Cells(1, dCol+2))=cRng

Fully qualify the cells references inside the range to avoid errors being raised.

Good point from @marucciboy2 re using the column count to ensure width match.

sht2.Range(sht2.Cells(1, dCol), sht2.Cells(1, dCol+cRng.columns.count-1))=cRng

Upvotes: 1

Related Questions