Excel VBA -For Next Loop Copies Same Row Multiple Times

Question

The For Next Loop in the my code is supposed to copy 1 row of numeric data 19 columns wide, one row at a time, for some reason the first row of the source file will copy twice (for i = 1 and i = 2) then twice for the second row in the source data (i = 3 and 4) Row number variables are being incrementally increased each loop. Any ideas what I'm doing wrong, once again thanks in advance for any assistance

Option Explicit

Sub CopyColumnTest()

Const FILE1 As String = "c:\users\john\documents\cvi - excel files\project - Cash Drawer Report\New Folder\PreFlashSales.xls"
Const FILE2 As String = "c:\users\john\documents\cvi - excel files\project - Cash Drawer Report\New Folder\2016 Flash Sales-JFP.xls"
Const Sheet1 As String = "Sheet1"
Const Sheet2 As String = "Actual"
Dim Col As Integer
Dim Col1 As Integer
Dim Col2 As Integer
Dim Col3 As Integer
Dim RowNum As Integer
Dim RowNum1 As Integer
Dim RowNum2 As Integer
Dim LastRow1 As Integer
Dim LastRow2 As Integer
Dim wb1 As Workbook, wb2 As Workbook
Dim i As Integer

i = 1
Col = 5
Col1 = 1
Col2 = 23
Col3 = 19

RowNum = 1


If wb1 Is Nothing Then Set wb1 = Workbooks.Open(FILE1)

If wb2 Is Nothing Then Set wb2 = Workbooks.Open(FILE2)


LastRow1 = wb1.Sheets(Sheet1).Cells(Rows.Count, "A").End(xlUp).row 'Last Row of Data in PreFlashSales Workbook

LastRow2 = wb2.Sheets(Sheet2).Cells(Rows.Count, "E").End(xlUp).row + 1 'Last Row of Data Previously Added in 2016 FlashSales-JFP
RowNum1 = LastRow2

    With wb1.Sheets(Sheet1)

        For i = 1 To LastRow1

        *** wb2.Sheets(Sheet2).Range(wb2.Sheets(Sheet2).Cells(RowNum1, Col), wb2.Sheets(Sheet2).Cells(RowNum1, Col2)).Value = _
            wb1.Sheets(Sheet1).Range(wb1.Sheets(Sheet1).Cells(RowNum, Col1), wb1.Sheets(Sheet1).Cells(RowNum & Col3)).Value ***

            RowNum = RowNum + 1
            RowNum1 = RowNum1 + 1
        Next i

    End With

End Sub

Scott Craner · Accepted Answer · 2016-06-23 20:43:08Z

1

When doing a direct assigning you do not need to loop you can just assign the value in bulk and directly:

Sub CopyColumnTest()

Const FILE1 As String = "c:\users\john\documents\cvi - excel files\project - Cash Drawer Report\New Folder\PreFlashSales.xls"
Const FILE2 As String = "c:\users\john\documents\cvi - excel files\project - Cash Drawer Report\New Folder\2016 Flash Sales-JFP.xls"
Const Sheet1 As String = "Sheet1"
Const Sheet2 As String = "Actual"
Dim Col As Long
Dim Col1 As Long
Dim Col2 As Long
Dim Col3 As Long
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet


Col = 5
Col1 = 1
Col2 = 23
Col3 = 19


If wb1 Is Nothing Then Set wb1 = Workbooks.Open(FILE1)
If wb2 Is Nothing Then Set wb2 = Workbooks.Open(FILE2)

'assign the sheets to variables to save typing
Set ws1 = wb1.Sheets(Sheet1)
Set ws2 = wb2.Sheets(Sheet2)

LastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row 'Last Row of Data in PreFlashSales Workbook

LastRow2 = ws2.Cells(Rows.Count, "E").End(xlUp).Row + 1 'Last Row of Data Previously Added in 2016 FlashSales-JFP


    With ws1
        ' when using With any object that is part of the with you just need to use '.' in front
        ws2.Range(ws2.Cells(LastRow2, Col), ws2.Cells(LastRow1 + LastRow2, Col2)).Value = .Range(.Cells(1, Col1), .Cells(LastRow1, Col3)).Value
    End With

End Sub

As to why you are copying two lines look at the last part of you Assignation Statement wb1.Sheets(Sheet1).Cells(RowNum & Col3)) You have a & instead of ,. Is should be wb1.Sheets(Sheet1).Cells(RowNum, Col3))

edited Jun 23, 2016 at 20:43

answered Jun 23, 2016 at 20:36

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott Craner Over a year ago

@John please mark as correct. click the check mark by the answer. It is something only you can do.

John Over a year ago

Scott Thanks Again!! - Now I know how to acknowledge great help

Collectives™ on Stack Overflow

Excel VBA -For Next Loop Copies Same Row Multiple Times

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related