CODEWITHSUNDEEP
vbaexcelloops
Mujahid Khan
Mujahid Khan

Reputation: 1

Looping in VBA to pick up next cell

i was wondering if you can help, i have 2 sheets a 'Masterfile' and 'Accounts' the aim is to copy each cell account number in Accounts worksheet starting from A2 to finish and copy this into B6 of the Masterfile worksheet. i need to save the file down on my desktop which i have written some code for but i cant seem to get my head around how to select the cell from accounts - copy into B6 - save and then move onto the next cell. can someone help?

Private Sub LOOPCELL()

Dim ws1 As Worksheet
Set ws1 = Sheets("Accounts")

Dim ws2 As Worksheet
Set ws2 = Sheets("Masterfile")

Dim lastRow As Integer
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

ws1.Range("A2:A" & lastRow).Copy Destination:=ws2.Range("B6")

Dim filepath1 As String
Dim Filename1 As String
Dim Filename2 As String
Dim Filename3 As String
Path = "C:\Users\mukhan\Desktop"
Filename1 = Range("B6")
Filename2 = Range("D6")
Filename3 = Range("C8")

ActiveWorkbook.SaveAs Filename:=Path & Filename1 & "-" & Filename2 & "-" & 
Filename3 & ".xlsb", FileFormat:=xlExcel12, CreateBackup:=False

End With
End Sub

Upvotes: 0

Views: 175

Answers (1)

Marcucciboy2
Marcucciboy2

Reputation: 3257

I had to make a few assumptions because you were not very specific in your question. The main thing is that my code assumes that both files are in the same workbook.

It selects the range A2:A__ in "Accounts" (down to the last filled value in column A) and copies all of that data at once over to "Masterfile" starting at B6.

Sub CopyRange()

    Dim ws1 As Worksheet
    Set ws1 = Sheets("Accounts")

    Dim ws2 As Worksheet
    Set ws2 = Sheets("Masterfile")

    Dim i As Integer
    For i = 2 To ws1.Cells(ws1.Rows.count, "A").End(xlUp).row
        ws1.Range("A" & i).Copy Destination:=ws2.Range("B6")
        ActiveWorkbook.SaveAs Filename:="C:\Users\mukhan\Desktop\Account " & ws1.Range("A" & i).Value2 & ".xlsb", FileFormat:=xlExcel12, CreateBackup:=False
    Next i

End Sub

Upvotes: 1

Related Questions