Go through columns and copy cells

Question

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.

SNicolaou · Accepted Answer · 2019-10-30 10:29:33Z

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

Plus, none of your activate / select statements are needed. You can use Header.xxx and samplelist.yyy straight away without the need to activate them first.
Now the code goes through the number of rows/columns I defined. But how can I manage to do a loop where it manages to copy the cell from one workbook (always same cell) to the other workbook where it should always paste the data one column further?

Collectives™ on Stack Overflow

Go through columns and copy cells

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related