Copying data from variable cells and pasting to fixed cells in another spreadsheet

0
votes

I am looking to build a macro in excel 2013 which can do the following:

  1. Copy data from individual cells in one spreadsheet (spreadsheet 1). The columns will never change but the rows will as it is a continually updated database of parts.

  2. Paste this data from spreadsheet 1 into the respective cell in another spreadsheet (spreadsheet 2). The cell in spreadsheet 2 will never change.

  3. Print out this document.

  4. Repeat step 1-3 for the next line of data in spreadsheet 1.

Note: Ideally the macro would allow for a user to highlight the portion of data in spreadsheet 1 that they want to copy into spreadsheet 2. The macro would then print off a version of spreadsheet 2 applicable to each line item in spreadsheet 1.

I would welcome any advice anyone can give me.

Current Code:

Sub SOAutomator()
'
' SOAutomator Macro
'

'
Range("A4").Select
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("A5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("C4").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("A7:D7").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("D4").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("I5:K5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("E4").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("E5:G5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("F4").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("H5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
    :=True, IgnorePrintAreas:=False
Windows("Shop Order Automator Macro.xlsm").Activate

Range("A5").Select
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("A5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("C5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("A7:D7").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("D5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("I5:K5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("E5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("E5:G5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("F5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("H5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
    :=True, IgnorePrintAreas:=False
Windows("Shop Order Automator Macro.xlsm").Activate

Range("A6").Select
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("A5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("C6").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("A7:D7").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("D6").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("I5:K5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("E6").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("E5:G5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("F6").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("H5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
    :=True, IgnorePrintAreas:=False
Windows("Shop Order Automator Macro.xlsm").Activate

Range("A7").Select
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("A5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("C7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("A7:D7").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("D7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("I5:K5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("E7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("E5:G5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("F7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("H5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
    :=True, IgnorePrintAreas:=False
Windows("Shop Order Automator Macro.xlsm").Activate

Range("A8").Select
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("A5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("C8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("A7:D7").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("D8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("I5:K5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("E8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("E5:G5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("F8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("H5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
    :=True, IgnorePrintAreas:=False
Windows("Shop Order Automator Macro.xlsm").Activate

Range("A9").Select
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("A5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("C9").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("A7:D7").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("D9").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("I5:K5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("E9").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("E5:G5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("F9").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("H5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
    :=True, IgnorePrintAreas:=False
Windows("Shop Order Automator Macro.xlsm").Activate

Range("A10").Select
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("A5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("C10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("A7:D7").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("D10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("I5:K5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("E10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("E5:G5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("F10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("H5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
    :=True, IgnorePrintAreas:=False
Windows("Shop Order Automator Macro.xlsm").Activate

Range("A11").Select
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("A5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("C11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("A7:D7").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("D11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("I5:K5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("E11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("E5:G5").Select
ActiveSheet.Paste
Windows("Shop Order Automator Macro.xlsm").Activate
Range("F11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Job Sheet Templates.xlsx").Activate
Range("H5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
    :=True, IgnorePrintAreas:=False
Windows("Shop Order Automator Macro.xlsm").Activate

End Sub
1
vbaexcelcopy-paste
I doubt you're going to find anyone here that will simply give you the code you need... Your best bet is to start off trying to do a bit of it yourself and post questions here as you find yourself getting stuck - We're here to help you get over stuck points / offer advice, not give code... – John Bustos
you could start by turning on the macro recorder, which will give you some code to play around with. It'll give you Select worksheet, select cell or range, copy and paste, pastespecial, print the document. – Our Man in Bananas
+1 for the great advices! – user2140173
Thank you for both your comments. I have run the macro recorder in the past. Where I get stuck is in running a command which only processes the information highlighted when the macro is run? Is there a way to do this? Sorry for being vague, I am very new to VBA and I am just trying to write a piece of code that will help out a work colleague. – user2413846
Okay I am making progress with this. I have thought of a work around which makes the macro slightly easier to write. Is it possible however, to end the sub if the macro encounters an empty cell? I have pasted the code I have so far into the main question above. – user2413846

1 Answers

0
votes

Compiles OK, but not tested...

Sub SOAutomator()

Dim shtTemplate As Worksheet
Dim shtMacro As Worksheet
Dim rw As Range


    'adjust sheet names as needed...
    Set shtTemplate = Workbooks("Job Sheet Templates.xlsx").Sheets("Sheet1")
    'assumes macro is in "macro" workbook
    Set shtMacro = ThisWorkbook.Sheets("Data")     

    Set rw = shtMacro.Rows(4) 'start on row 4

    'run until hit an empty cell in the first column...
    Do While Len(rw.Cells(1).Value) > 0

        With rw
            .Cells(1).Copy shtTemplate.Range("A5")
            .Cells(3).Copy shtTemplate.Range("A7:D7")
            .Cells(4).Copy shtTemplate.Range("I5:K5")
            .Cells(5).Copy shtTemplate.Range("E5:G5")
            .Cells(6).Copy shtTemplate.Range("H5")
        End With

        shtTemplate.PrintOut From:=1, To:=1, Copies:=1, _
                     Collate:=True, IgnorePrintAreas:=False

        Set rw = rw.Offset(1, 0) 'next row

    Loop

    ThisWorkbook.Activate

End Sub