How to copy data from a cell in sheet1 to sheet2, looping through each cell?

Question

How can I cycle through sheet1 to see if there is data in that cell?

If there is no data then go to the next cell.
If there is data in the next cell paste it into sheet2.

The criteria are:

I cannot use a set range it will change as the data changes in sheet1.
I can keep sheet names a constant such as sheet1 and sheet2.

I found a way using columns and or rows yet that code has a major issue. If there is no starting data in the first cell it will not copy anything in the entire row and or column.

I am posting the code I worked with to check the data in columns but if there is no starting data it will skip the whole row.

Sub CopytoImport()
    Dim wb As Workbook
    Dim iCol As Long
    Dim ws As Worksheet

    Sheets("sheet2").Cells.ClearContents

    ' Loop through the column
    For iCol = 1 To 22 ' Call out columns I cannot set this every time it should look threw all cells
        With Worksheets("sheet1").Columns(iCol)
            ' Check tht column is not empty
            If .Cells(1, 1).Value = "" Then
                'Nothing in this column
                'Do nothing
            Else
                ' Copy the coumn to te destination
                Range(.Cells(1, 1), .End(xlDown)).Copy _
                    Destination:=Worksheets("sheet2").Columns(iCol).Cells(1, 1)
            End If
        End With
    Next iCol

    ActiveWorkbook.Save
End Sub


Function runcode()
    Call CopytoImport
End Function

You can use the macro recorder or look for similar questions here on SO. — BigBen
and that would not help if i am going cell to cell looking for data and if i found it copy that to sheet2 — user10144863
i want to use all the cells this is only some test code to get me started — user10144863

roses56 roses56 · Accepted Answer · 2020-02-05T17:40:19

Cells(1, 1) is just RANGE.("A1") you are only operating on this cell in your code. You would need Cells(1, iCol) to account for what column you are on during your loop.

You might also need a nested loop since you are looping through rows as well. The basic outline of a nested loop is as follows. Note the Cells(1,1) is replaced with the i and j representing what row and what column we are on. This might not be the fastest way to achieve the results you want but it sounds like this is what you are asking for help with. You will also need to define a lastrow (with a + 1 at the end to get the next blank cell) in your Sheet2 for when you paste the data. You would put this right under where the loop starts going through rows. This is so the lastrow of your sheet2 is recalculated each time data is being moved to that sheet. I am not going to re-write your code since you stated it is not complete but here is an example that should help you.

For j = 5 To lastcolumn

    For i = 5 To lastrow

        Dim lastrow2 As Long
        lastrow2 = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row + 1

        If Worksheets(2).Cells(i, j).Value <> 0 Then
        Worksheets(1).Range("C" & lastrow2).Value = Worksheets(2).Cells(i, j).Value
        Worksheets(1).Range("B" & lastrow2).Value = Worksheets(2).Cells(2, j).Value

        End If

    Next i

Next j

To find your lastrow:

dim lastrow as long
lastrow = Range("A" & rows.count).End(xlup).Row ' or whatever column contains the data

To find your last column

Dim lastcolumn As Long

lastcolumn = Worksheets(2).Cells(2, Columns.Count).End(xlToLeft).Column

How to copy data from a cell in sheet1 to sheet2, looping through each cell?

1 Answers