Loop for Copy/Paste until empty row

[kurigami]

Hi everyone!


I'm hoping someone can help me with VBA as I'm about useless, especially when it comes to loops. I have a workbook that can have data entered into cells C4 and E4, and it generates a snapshot view, we then print the view. I need to automate this task if possible so that you don't have to manually run 60 snapshots.


What I've done to set this up is created another worksheet named "Reps" and placed data into columns A:B. There are headers in row 1, so that row can be ignored, however, I'm not sure how to write the VBA that would start in cell B2 on the "Reps" sheet and copy it to cell C4 on the "Month" workhseet and then copy A2 from the "Reps" sheet and paste to cell E4 on the "Month" sheet. Once the copy and paste was done the cell needs to change on the "Month" sheet so that the data updates, and then macro "Print_SnapShot" can be run. Once this is done it would need to loop and repeat these exact steps, only going down one row at a time on the "Reps" sheet (the cells to paste into are static) until it hits an empty row.


For the life of my I can't figure this out. If anyone could help it would be really appreciated! Thanks so much!

 

[gallen]

Re: Help! Loop for Copy/Paste until empty row

I tried breaking down what you said and confused at this part:

Once this is done it would need to loop and repeat these exact steps, only going down one row at a time on the "Reps" sheet(the cells to paste into are static) until it hits an empty row.

So say you copy from Reps!B2 and paste to Month!C4.

You then copy from Reps!B3 and paste into the same cell thus overwriting the previous values?

 

[kurigami]

Re: Help! Loop for Copy/Paste until empty row

I tried breaking down what you said and confused at this part:



So say you copy from Reps!B2 and paste to Month!C4.

You then copy from Reps!B3 and paste into the same cell thus overwriting the previous values?

Yep, you are exactly correct. The whole point of the Month sheet is to pull a snapshot for each representative, so once the Print Macro is done, then I am pulling the next representative by copying the next row on the Reps sheet and pasting them into the same cells on the Month worksheet, thus overwriting the previous values that were pasted in just a few seconds earlier. Pattern would look like this:
Sheet Reps > copy cell B2
Sheet Month > paste into C4
Sheet Reps > copy cell A2
Sheet Month > paste into E4
Run Macro Print_SnapShot

Then loop to row 3 on the Reps sheet
Sheet Reps > copy cell B3
Sheet Month > paste into C4
Sheet Reps > copy cell A3
Sheet Month > paste into E4
Run Macro Print_SnapShot

That would need to keep looping until it hits the first empty cell in Column B.

 

[MARK858]

Re: Help! Loop for Copy/Paste until empty row

Assuming that having the month sheets cells as values isn't an issue try...

Sub CopyVal()
    Dim i As Long
    For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
        Sheets("Month").Cells(4, "C").Value = Sheets("Reps").Cells(i, "B").Value
        Sheets("Month").Cells(4, "E").Value = Sheets("Reps").Cells(i, "A").Value
        Print_SnapShot
    Next
End Sub

 

[kurigami]

Re: Help! Loop for Copy/Paste until empty row

That did the trick, thank you so much Mark! Was a lot simpler than what I was thinking

 

[MARK858]

Re: Help! Loop for Copy/Paste until empty row

You're welcome but it should really be

Sub CopyVal()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 2 To  Sheets("Reps").Range("B" & Rows.Count).End(xlUp).Row
        Sheets("Month").Cells(4, "C").Value = Sheets("Reps").Cells(i, "B").Value
        Sheets("Month").Cells(4, "E").Value = Sheets("Reps").Cells(i, "A").Value
        Print_SnapShot
    Next
    Application.ScreenUpdating = True
End Sub