CODEWITHSUNDEEP
excelvba
TSZ
TSZ

Reputation: 1

Go through columns and copy cells

I wrote code that goes to each row if there is data and copies the data to a different file in specific cells.

Now I want to go through each column instead of row till there is no data left.

My code for rows that is working is:

Sub Row_copying()

'load my workbooks

Dim Header As Workbook
Workbooks.Open FileName:="/Users/Header.xlsx"
Set Header = Workbooks("Header.xlsx")

Dim samplelist As Workbook
Workbooks.Open FileName:="/Users/samplelist.xlsx"
Set samplelist = Workbooks("samplelist.xlsx")

samplelist.Activate

' Loop through each row that has data
Dim lRow As Long

For lRow = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    If Range("A" & lRow).Value <> "" Then
        'copy cell
        Range("D" & lRow).Copy
        Header.Activate
        Range("K5:M5").Select
        ActiveSheet.Paste

        samplelist.Activate
        Range("H" & lRow).Copy
        Header.Activate
        Range("F5:G5").Select
        ActiveSheet.Paste

        Dim DName As String, dataname As String, path As String
        samplelist.Activate
        path = "/Users/newdata/"
        DName = "sample_"
        dataname = path & DName & Format(Range("A") & lRow.Value, "000") & ".xlsx"
        Header.Activate
        ActiveWorkbook.SaveAs FileName:= dataname
    End If
    samplelist.Activate
Next lRow
Workbooks("samplelist.xlsx").Close
End Sub

I could check how many columns have data by changing the lRow to

lCol = Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox"last Column: "&lCol

I could not manage that it goes through each column and copy the cells.

Upvotes: 0

Views: 249

Answers (1)

SNicolaou
SNicolaou

Reputation: 550

Instead of using Range() to reference the cells you are checking, you could use Cells(x,y). Since you know the number of rows and columns you have, you can have a nested For i.e. For Each Row Loop All Columns.

Option Explicit

Public Sub sCopy()

  Dim numberOfColumns As Long, numberOfRows As Long
  Dim x As Long, y As Long
  Dim ws As Worksheet

  'set number of rows/columns
  'set workbooks / worksheets

  For x = 1 To numberOfRows
    For y = 1 To numberOfColumns
      If ws.Cells(x, y).Value <> "" Then
       'Do what you have to do
      End If
    Next y
  Next x

End Sub

Upvotes: 1

Related Questions