CODEWITHSUNDEEP
excelvba
user10144863
user10144863

Reputation:

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

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

The criteria are:

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

Upvotes: 1

Views: 277

Answers (1)

roses56
roses56

Reputation: 130

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

Upvotes: 0

Related Questions