CODEWITHSUNDEEP
excelvbavba7vba6
Fah
Fah

Reputation: 203

how to set values between worksheets after IF statement using 2 last rows

I am trying to copy and paste the entire entire columns values from sheet named Hey, to another columns in a sheet named final.

I dont want to use copy, paste or select option.However, my code is copying only the 1st line.

IntLastRow = Sheets("Hey").Cells(Cells.Rows.Count, "A").End(xlUp).Row
' IntRLastRow = Sheets("Interdiction Review").Cells(Cells.Rows.Count, "A").End(xlUp).Row

If Sheets("Hey").Range("H2") = "" Then
    Sheets("Final").Range("A2").Value = Sheets("Hey").Range("G2:G" & IntLastRow).Value
ElseIf Sheets("Hey").Range("H2") <> "" Then
    Sheets("Final").Range("A2").Value = Sheets("Hey").Range("H2:H" & IntLastRow).Value
EndIf

maybe do you know what I am doing wrong?

I need to copy dates from 2 or more columns to another column located in a different worksheet.

However, I am not sure if I can use 2 last row in the same line. I am trying this code, but only paste the value of 1 line.

intrlastrow = Sheets("Final").Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
 
 Debug.Print intrlastrow
    If Sheets("Hey").Range("AJ2") = "" Then
            Sheets("Final").Range("A" & intrlastrow, "A" & IntLastRow).Value = Sheets("Hey").Range("AI2:AI" & IntLastRow).Value
        ElseIf Sheets("Hey").Range("AJ2") <> "" Then
            Sheets("Final").Range("A" & intrlastrow, "A" & IntLastRow).Value = Sheets("Hey").Range("AJ2:AJ" & IntLastRow).Value
        End If

I need to add the values of 2 or more columns from sheet named HEY to the same column in the destination sheet (Final sheet)

Upvotes: 0

Views: 57

Answers (1)

eirikdaude
eirikdaude

Reputation: 3254

Unless the range you are trying insert the values from is as large as the one you are taking them from, only the data which fits will be copied. Changing your code to something like

Option Explicit

Sub test()
    Dim IntLastRow As Long
    
    IntLastRow = Sheets("Hey").Cells(Cells.Rows.Count, "A").End(xlUp).Row
    ' IntRLastRow = Sheets("Interdiction Review").Cells(Cells.Rows.Count, "A").End(xlUp).Row
    
    If Sheets("Hey").Range("H2") = "" Then
        Sheets("Final").Range("A2:A" & IntLastRow).Value = Sheets("Hey").Range("G2:G" & IntLastRow).Value
    ElseIf Sheets("Hey").Range("H2") <> "" Then
        Sheets("Final").Range("A2:A" & IntLastRow).Value = Sheets("Hey").Range("H2:H" & IntLastRow).Value
    End If
End Sub

should do the trick.

You probably notice that I've changed Sheets("Final").Range("A2") to Sheets("Final").Range("A2:A" & IntLastRow), similar to what you've done to decide what range to copy values from.

Upvotes: 1

Related Questions