CODEWITHSUNDEEP
excelcopy-pastevba
Nick
Nick

Reputation: 39

Copying cell data from one worksheet to another

I have attempted to create an Excel VBA function that will copy the data in cells from worksheet 1 and paste them into worksheet 2. Ideally, I'm checking cells AF6, AF7... all the way to whatever the value of "rows" is. If the value inside the cell is equal to 1, the code will copy all of the listed columns from row "C" (in the first instance row 6) and paste them into row 2 (6-4). Some cells contain numbers, others contain strings. I'm stuck--if anyone can solve my problem, I'd really appreciate it.

Public Sub copy_cells(rows As Integer)

Dim C As Integer

For C = 6 To C = rows   
    If Worksheets("sheet1").Range("AF" & C).Value = 1 Then
        Sheet2.Range("B" & C).Value = Sheet1.Range("B" & C - 4).Value
        Sheet2.Range("E" & C).Value = Sheet1.Range("C" & C - 4).Value
        Sheet2.Range("I" & C).Value = Sheet1.Range("D" & C - 4).Value
        Sheet2.Range("J" & C).Value = Sheet1.Range("E" & C - 4).Value
        Sheet2.Range("R" & C).Value = Sheet1.Range("F" & C - 4).Value
        Sheet2.Range("V" & C).Value = Sheet1.Range("G" & C - 4).Value
        Sheet2.Range("C" & C).Value = Sheet1.Range("H" & C - 4).Value
        Sheet2.Range("F" & C).Value = Sheet1.Range("J" & C - 4).Value
        Sheet2.Range("G" & C).Value = Sheet1.Range("K" & C - 4).Value
        Sheet2.Range("D" & C).Value = Sheet1.Range("N" & C - 4).Value            
    End If
    C = C + 1
Next C

End Sub

Upvotes: 0

Views: 2923

Answers (2)

chuff
chuff

Reputation: 5866

Your code is very close to running. Two changes: Correction of the iteration portion of the FOR loop; and the removal of the unnecessary explicit iteration of C at the bottom of the loop.

  Option Explicit

  Public Sub copy_cells(rows As Integer)

     Dim C As Integer
     For C = 6 To rows
        If Worksheets("Sheet1").Range("AF" & C).Value = 1 Then
           Sheet2.Range("B" & C).Value = Sheet1.Range("B" & (C - 4)).Value
           Sheet2.Range("E" & C).Value = Sheet1.Range("C" & (C - 4)).Value
           Sheet2.Range("I" & C).Value = Sheet1.Range("D" & (C - 4)).Value
           Sheet2.Range("J" & C).Value = Sheet1.Range("E" & (C - 4)).Value
           Sheet2.Range("R" & C).Value = Sheet1.Range("F" & (C - 4)).Value
           Sheet2.Range("V" & C).Value = Sheet1.Range("G" & (C - 4)).Value
           Sheet2.Range("C" & C).Value = Sheet1.Range("H" & (C - 4)).Value
           Sheet2.Range("F" & C).Value = Sheet1.Range("J" & (C - 4)).Value
           Sheet2.Range("G" & C).Value = Sheet1.Range("K" & C - 4).Value
           Sheet2.Range("D" & C).Value = Sheet1.Range("N" & C - 4).Value
        End If
     Next C

  End Sub

Upvotes: 1

mr.Reband
mr.Reband

Reputation: 2430

Change your For line to:

For C = 6 To rows

C = rows evaluates to false (0), and you never reach the code inside the for loop (For 6 to 0)

Upvotes: 2

Related Questions