Automate copy/paste/print via macros/vba

[Stephen_W]

Hello,
Relatively simple task to complete but new to macros/vba. I have a spreadsheet containing a wealth of data and another spreadsheet that does vlookups of the data based on the product number entered into cell B5. I need to copy-paste cell A2 from data sheet (Specs.xlsx) into the other workbook (Fact Sheet.xlsx) and print. Then copy/past A3 from data sheet to other workbook and print. Continue doing this for hundreds of rows and then stop if there is no data in the cell. Started a macro and it looks like this:

Windows("Specs.xlsx").Activate
Range("A2").Select
Selection.Copy
Windows("Fact Sheet.xlsx").Activate
Range("B5").Select
ActiveSheet.Paste
ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, _
IgnorePrintAreas:=False
Windows("Specs.xlsx").Activate
Range("A3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Fact Sheet.xlsx").Activate
ActiveSheet.Paste
ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, _
IgnorePrintAreas:=False
End Sub

Any help is greatly appreciated.
Regards.

 

[HackSlash]

There are a bunch of built-in shortcuts to do things like this.

For one row use this:

Range("A2").End(xlToRight).Select

To select all contiguous cells use this:

Range("A2").CurrentRegion.Select

 

[tonyyy]

Stephen,

Welcome to the Board!

You might consider the following...

Sub CopySpecs()
Dim i As Long
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = Workbooks("Specs.xlsx")
Set wb2 = Workbooks("Fact Sheet.xlsx")
i = 2

Do
    wb2.Sheets(1).Range("B5").Value = _
        wb1.Sheets(1).Range("A" & i)
    wb2.Sheets(1).PrintOut Copies:=2, Collate:=True, _
        IgnorePrintAreas:=False
    i = i + 1
Loop Until wb1.Sheets(1).Range("A" & i) = ""
End Sub

I strongly suggest you test on copies of your workbooks before proceeding. It might be that the macro runs more quickly than your printer can handle, so you may receive print buffer errors or just find that some pages aren't printed. If that's the case we may need to add some variation of DoEvents or allow for an occasional pause.

Cheers,

tonyyy