CODEWITHSUNDEEP
excelvba
Zaur
Zaur

Reputation: 123

Copying range to another sheet

I have 3 sheets: DATA, BUILD and RESULT.
DATA - contains all data
BUILD - 1 row that is iterated through VBA procedure (below) and a command button.
RESULT - where I need to put every iterated row of BUILD table.

The following part of the code is expected to add new row to a RESULTS table.
In a RESULTS sheet, rows are copied sometimes in row 2567, sometimes in 237 etc.
I can't understand the logic how VBA determines the row it puts copied rows.

Sheets("RESULTS").Range("A2" & i + 1).Value = Sheets("BUILD").Range("D4").Value 'League Name
Sheets("RESULTS").Range("B2" & i + 1).Value = Sheets("BUILD").Range("E4").Value 'Home Team
Sheets("RESULTS").Range("C2" & i + 1).Value = Sheets("BUILD").Range("F4").Value 'Away Team

This is the full code:

Sub btn_NextMatch()
    Application.ScreenUpdating = False
    Application.Volatile
    Dim Last_row As Double
    Dim Last_Col As Integer
    Dim i As Integer
    Dim sheet As String
    sheet = ActiveSheet.Name
    
    Sheets("BUILD").Select
    i = Range("A1").Value
     
    Sheets("DATA").Select
    Last_row = Range("A" & Rows.Count).End(xlUp).Row
    Last_Col = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column
    Last_colletter = Split(Cells(1, Last_Col).Address, "$")(1)
    If i = Last_row Then
        i = 1
    End If
    
    Sheets("BUILD").Range("C2").Value = Sheets("DATA").Range("C" & i + 1).Value 'MatchID
            
    Sheets("BUILD").Range("D4").Value = Sheets("DATA").Range("D" & i + 1).Value 'League Name
    Sheets("BUILD").Range("E4").Value = Sheets("DATA").Range("F" & i + 1).Value 'Home Team
    Sheets("BUILD").Range("F4").Value = Sheets("DATA").Range("G" & i + 1).Value 'Away Team
    
    Sheets("BUILD").Select
    
    If i = Last_row Then
        Range("A1").Value = 1
    Else
        Range("A1").Value = i + 1
        Sheets("RESULTS").Range("A2" & i + 1).Value = Sheets("BUILD").Range("D4").Value 'League Name
        Sheets("RESULTS").Range("B2" & i + 1).Value = Sheets("BUILD").Range("E4").Value 'Home Team
        Sheets("RESULTS").Range("C2" & i + 1).Value = Sheets("BUILD").Range("F4").Value 'Away Team
    End If
    
    Application.ScreenUpdating = True
    
    Sheets(sheet).Select
    
End Sub

Upvotes: 0

Views: 79

Answers (2)

Darrell H
Darrell H

Reputation: 1886

I do not recommend using offset to counter the previous problem. Here are a few items corrected in the code:

  1. Declare last row, column, etc as Long. There are over 1 million rows in Excel and integer will only handle up to 32,767. After that you will overload the value.

  2. Shorten the workbook names by setting them as variables. No need to Dim as string.

  3. Avoid Select and Activate by qualifying your worksheets and variables. That means giving as full information as needed to specify the location ThisWorkbook.Worksheets("Sheet1").Range("A1") versus Range("A1") that could be any sheet. Not only will this ensure proper locations, but will speed up your code by avoiding changing sheets.

     Sub btn_NextMatch()
 Application.ScreenUpdating = False
 Application.Volatile
 Dim Last_row As Long
 Dim Last_Col As Long
 Dim i As Long

 Dim wks1 As Worksheet
 Dim wks2 As Worksheet
 Dim wks3 As Worksheet

 Set wks1 = ThisWorkbook.Worksheets("BUILD")
 Set wks2 = ThisWorkbook.Worksheets("DATA")
 Set wks3 = ThisWorkbook.Worksheets("RESULTS")

 i = wks1.Range("A1").Value

 Last_row = wks2.Range("A" & Rows.Count).End(xlUp).Row
 Last_Col = wks2.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlValues).Column
 Last_colletter = Split(wks2.Cells(1, Last_Col).Address, "$")(1)
 If i = Last_row Then
     i = 1
 End If

     wks1.Range("C2").Value = wks2.Range("C" & i + 1).Value 'MatchID
     wks1.Range("D4").Value = wks2.Range("D" & i + 1).Value 'League Name
     wks1.Range("E4").Value = wks2.Range("F" & i + 1).Value 'Home Team
     wks1.Range("F4").Value = wks2.Range("G" & i + 1).Value 'Away Team

 If i = Last_row Then
    wks1.Range("A1").Value = 1
 Else
     wks1.Range("A1").Value = i + 1
     wks3.Range("A" & i + 1).Value = wks1.Range("D4").Value 'League Name
     wks3.Range("B" & i + 1).Value = wks1.Range("E4").Value 'Home Team
     wks3.Range("C" & i + 1).Value = wks1.Range("F4").Value 'Away Team
 End If

 Application.ScreenUpdating = True
End sub

Upvotes: 1

Adam Grinbergs
Adam Grinbergs

Reputation: 1

Darrel H. is right. If you are concatenating the iteration you're on to the cell address you are doing equivalent of: "A2" & 25 becomes A225, where you really wanted to have "A27". A way around this is to use the offset function. Your code rewritten would be like this:

Sheets("RESULTS").Range("A2" & i + 1).offset(rowoffset:=i + 1).value = Sheets("BUILD").Range("D4").Value 'League Name'

Upvotes: 0

Related Questions