Loop for multiple lines of copying and pasting

[Phippsy]

Hi all,

Looking for some help on this. My colleague and I have developed a pricing tool that we use for costing component parts. Often these component parts are very similar in terms of manufacture with the only difference being that the size of the component alters. We've set the spreadsheet up so that all we need to do is enter the line number and this alters the size of the component automatically and gives a price, which we then copy back to the corresponding line in our data schedule. The only problem with this is that often we're doing this for 100's of lines which can become quite monotonous.

This code for one line is basically what we'd like the routine to do for all lines:

Sub AUTOCOPYPASTE()'
' AUTOCOPYPASTE Macro
'


'
    Sheets("Imported Lines").Select
    Range("A2").Select
    Selection.Copy
    Sheets("Routes").Select
    Range("B8").Select
    ActiveSheet.Paste
    Sheets("BoM Report").Select
    Range("AA80").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Imported Lines").Select
    Range("G2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End Sub

What we'd like to be able to do if possible, is to run a loop that would perform the above for every line until it finds a blank cell in column A in "imported lines". So A2 becomes A3 and the number in this cell gets copied to "routes" B8. Then the result in "BoM Report" AA80 is copied and pasted to G3 in "imported lines" and so forth.

Apologies if this is unclear, I've written it a few times to try to make it as concise as possible without making it nonsensical! let me know if you need any more info, happy to supply as much info as is needed

Thank you very much in advance

Tony

 

[RoryA]

You can simplify that code and add a loop like this:

Sub AUTOCOPYPASTE()
   Dim lRow as long
    With Sheets("Imported Lines")
       for lrow = 2 to .cells(.rows.count, "A").End(xlUp).row
           .Cells(lRow, "A").Copy Sheets("Routes").Range("B8")
           .Cells(lRow, "G").Value2 = Sheets("BoM Report").Range("AA80").Value2
       next lrow
    End With

End Sub

 

[Phippsy]

Rory,

That is brilliant. Thanks for coming back so quickly!

Cheers

Tony

 

[RoryA]

Glad to help.

 

[Phippsy]

Hi Rory,

I was wondering if you might be able to assist us one step further with this. Is there a straightforward way, as part of the loop, that the spreadsheet could automatically create a pdf of the "bom report" sheet in the same folder as the spreadsheet for each of the lines entered? This would enable us have snapshot price breakdowns of each line. i.e Line 1 - copy price, paste to corresponding column, then create a pdf before moving on to line 2? It could just be saved as "line 1" and so forth......

Thanks again in advance

Cheers

Tony

 

[RoryA]

Yes, just add:

Sheets("BoM Report").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        ThisWorkbook.Path & "\Line" & lRow - 1 & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:= _
        False, IgnorePrintAreas:=False, OpenAfterPublish:=False

inside the For ... Next loop.

 

[Phippsy]

Rory,

Thank you again, you have no idea how much that has helped us. If you're ever down in south wales, I'll have to buy you a few beers!

Cheers

Tony

 

[RoryA]

I'll stop off next time I'm on my way down to Cornwall!!