Macro Do Until Loop Copy from list of values paste into single cell (e.g. b1)

the_natural · Apr 22nd 2016

Hi,

This is my first post here, so that you in advance for your help. What a great community!

I am trying to write a macro that will loop through a list of values of an undetermined number of rows and one by one copy and paste the values into a single cell, each time through loop replacing the value that was just pasted into the single cell, which is referenced by a report template and auto-populates data based on the id of the number

Here is an example of what the table will look like:

A B
1 231 234
2 232
3 233
4 234
5 235
6 236
7 237

231 would be copied and pasted into B1, then 232 would be copied and pasted into B1, then 233 would be copied and pasted into B1, then 234 would be copied and pasted into B1.....and so on and so forth. In between the copy and past steps there are other steps to add images to a worksheet and save as a pdf.

I wrote this script to accomplish the goal:

Code

Sub Report()
'
' Report Macro
'
' Keyboard Shortcut: Ctrl+Shift+G
'
' this section just copies a selection of cells from on worksheet and moves it to another worksheet filters it and copies filtered list to yet another worksheet.
    Application.ScreenUpdating = False
    Selection.Copy
    Sheets("Master Sheet").Select
    Range("A6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Range("$A$5:$BS$410").AutoFilter Field:=7, Criteria1:="2"
    Selection.Copy
    Sheets("Report").Select
    Range("A1").Select
    ActiveSheet.Paste
      ' This section does the operation outlined at beginning of post.  
    Range("A1").Select
    Do Until IsEmpty(ActiveCell.Value)
        Selection.Copy
        Range("B1").Select
        ActiveSheet.Paste
        Application.Run "PERSONAL.XLSB!ErasePhoto"
        Application.Run "PERSONAL.XLSB!PhotoPlace"
        ActiveWindow.ScrollRow = 1
        Application.CutCopyMode = False
        ChDir "C:"
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("B3").Value _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
        Application.Run "PERSONAL.XLSB!ErasePhoto"
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Display More

It goes through once, but doesn't loop. I'm not sure why? Thanks!!!!

skywriter · Apr 22nd 2016

Re: Macro Do Until Loop Copy from list of values paste into single cell (e.g. b1)

Please choose to edit your post, then select all the code, push the # icon on the toolbar and re-save your post.

Your code will then look like this.

Code

Sub Test()
End Sub

This is called using code tags and it's how you should post all code to this board.

the_natural · Apr 22nd 2016

Re: Macro Do Until Loop Copy from list of values paste into single cell (e.g. b1)

Thank your for the tip! I just changed it and will do so in the future.

skywriter · Apr 22nd 2016

Re: Macro Do Until Loop Copy from list of values paste into single cell (e.g. b1)

Quote from the_natural;769718

Thank your for the tip! I just changed it and will do so in the future.

Awesome, thanks. :cheers:
I'll take a look at it.

skywriter · Apr 22nd 2016

Re: Macro Do Until Loop Copy from list of values paste into single cell (e.g. b1)

This sounds like what you want.

Code

Dim r As Range
    For Each r In Range("A1", Range("A" & Rows.Count).End(xlUp))
        Range("B1").Value = r.Value
        Application.Run "PERSONAL.XLSB!ErasePhoto"
        Application.Run "PERSONAL.XLSB!PhotoPlace"
        ActiveWindow.ScrollRow = 1
        Application.CutCopyMode = False
        ChDir "C:"
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("B3").Value _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
        Application.Run "PERSONAL.XLSB!ErasePhoto"
    Next r

Display More

the_natural · Apr 22nd 2016

Re: Macro Do Until Loop Copy from list of values paste into single cell (e.g. b1)

Thank you so much skywriter! That worked like a charm. Thanks for helping me understand another way to approach it.

skywriter · Apr 22nd 2016

Re: Macro Do Until Loop Copy from list of values paste into single cell (e.g. b1)

My pleasure, thanks for the feedback. :cool:

Macro Do Until Loop Copy from list of values paste into single cell (e.g. b1)

Participate now!

Share