Excel macro loop through rows and copy to another sheet

1
votes

I'm new to VBA and trying to acheive this in Excel:

I have a sheet1 where there are rows with values. I have created a button which executes and copy the first row of data to another sheet(Sheet2), into some predefined cells (i.e. value in Cell A1 goes into for instance B3). Then I'm going to save that into a pdf.

Now I want my code to be more dynamic and do this for each of the rows in Sheet1. So first all values in A1-D1 gets copied to sheet2, create pdf, then next row (A2-D2) to sheet 2, create pdf and so on.

How can I acheive this?

My code that works fine for one row: 

Private Sub CommandButton1_Click()

 'Get name
   Worksheets("Sheet2").Range("B5").Value = Worksheets("Sheet1").Range("V2").Value &     " " & Worksheets("Sheet1").Range("W2").Value

 'Get adress
 Worksheets("Sheet2").Range("B6").Value = Worksheets("Sheet1").Range("AB2").Value

 'Create pdf for this row
 RDB_Worksheet_Or_Worksheets_To_PDF ()


End Sub
2
loopsexcelpdfvba
Can you show us what have you tried? Questions asking for code must demonstrate a minimal understanding of the problem being solved. Include attempted solutions, why they didn't work, and the expected results. See also: Stack Overflow question checklist - Siddharth Rout
Do you mind providing the macro code you have so far? - user1064248
Added my current code - Rupal
Now Where is the Source Data in sheet1 i.e., range, which needs to be Copied to other sheets. if the source data is in a1:d1 means i have a solution. - Punith GP

2 Answers

2
votes

You need a loop to iterate through all cells in your V column.

Private Sub CommandButton1_Click()

    Dim ws1 As Worksheet
    Set ws1 = Sheets(1)

    Dim ws2 As Worksheet
    Set ws2 = Sheets(2)

    Dim cell As Range
    For Each cell In ws1.Range("V2:V" & ws1.Range("V" & Rows.Count).End(xlUp).Row)

        ws2.Range("B5") = cell & Chr(32) & cell.Offset(0, 1)
        ws2.Range("B6") = cell.Offset(0, 6)

        'RDB_Worksheet_Or_Worksheets_To_PDF ()

    Next

End Sub
2
votes

Solved!

Final code that works (for me):

Dim pointer As Integer
pointer = 2

Do While Not IsEmpty(Worksheets("Sheet1").Range("V" & pointer))

Private Sub CommandButton1_Click()

 'Get name
   Worksheets("Sheet2").Range("B" & pointer).Value = Worksheets("Sheet1").Range("V" & pointer).Value &     " " & Worksheets("Sheet1").Range("W"& pointer).Value

 'Get adress
 Worksheets("Sheet2").Range("B" & pointer).Value = Worksheets("Sheet1").Range("AB" & pointer).Value

 'Create pdf for this row
 RDB_Worksheet_Or_Worksheets_To_PDF ()

pointer = pointer + 1
Loop


End Sub