CODEWITHSUNDEEP
excelvba
freddhesse
freddhesse

Reputation: 1

Populating Worksheet with values from another Worksheet

First of all apologies if this topic or something similar to it has already been addressed. I am trying to populate a blank worksheet with the values contained in the other worksheet next to it.

Here is what I have been able to come up with so far:

Private Sub Populate_Click()

Dim i As Integer, j As Integer

For i = 2 To 2614

For j = 1 To 6

If ActiveWorksheet.IsEmpty(Cells(i, j)) = True Then

    If j = 1 Then

    ActiveWorksheet.Cells(i, j).Value = Worksheets("Import").Cells(i, 1)

    Else

        If j = 2 Then
        ActiveWorksheet.Cells(i, j).Value = Worksheets("Import").Cells(i, 8)
        Else

            If j = 3 Then
            ActiveWorksheet.Cells(i, j).Value = Worksheets("Import").Cells(i, 2)
            Else

                If j = 4 Then
                ActiveWorksheet.Cells(i, j).Value = Worksheets("Import").Cells(i, 5)
                Else

                    If j = 5 Then
                    ActiveWorksheet.Cells(i, j).Value = Worksheets("Import").Cells(i, 9)
                    Else

                        If j = 6 Then
                        ActiveWorksheet.Cells(i, j).Value = Worksheets("Import").Cells(i, 6)
                        Else

                        End If
                    End If
                End If
            End If
        End If
    End If
End If

Next j
  Next i
  End Sub

When I click the button on the excel sheet it just does not do anything. Would any of you be able to shine a light as to why?

The Reason I am using so many IF Statements is because I need to ready only specific columns of the "Import" at a time so that they can be populated in this specific order.

My knowledge in VBA is virtually nothing (I just started this today), I'm only somewhat familiar with logic. So I guess what I am saying is, please be patient.

Thanks in advance!

Upvotes: 0

Views: 50

Answers (1)

SJR
SJR

Reputation: 23081

Think we can tidy up the code a little using Select Case rather than lots of Ifs.

Be wary of ActiveSheet - better to specify a name as the active sheet may not be what you expect.

Private Sub Populate_Click()

Dim i As Long, j As Long

For i = 2 To 2614
    For j = 1 To 6
        If IsEmpty(ActiveSheet.Cells(i, j)) Then
            Select Case j
                Case 1
                    ActiveSheet.Cells(i, j).Value = Worksheets("Import").Cells(i, 1)
                Case 2
                    ActiveSheet.Cells(i, j).Value = Worksheets("Import").Cells(i, 8)
                Case 3
                    ActiveSheet.Cells(i, j).Value = Worksheets("Import").Cells(i, 2)
                Case 4
                    ActiveSheet.Cells(i, j).Value = Worksheets("Import").Cells(i, 5)
                Case 5
                    ActiveSheet.Cells(i, j).Value = Worksheets("Import").Cells(i, 9)
                Case 6
                    ActiveSheet.Cells(i, j).Value = Worksheets("Import").Cells(i, 6)
            End Select
        End If
    Next j
Next i

End Sub

Upvotes: 3

Related Questions