Macro to copy and paste

[Nymrod]

Hi there, I need to create a macro that will pick a pre-defined cell on a selected row, copy and paste it into another templated sheet. The macro then needs to return to the originaly selected row, pick another cell and paste it into the template sheet. It is required to copy and paste about 20 cells in total which run horizontally on the main sheet. I will then print off the template. Once it has completed I then need to be able to return to the main sheet, click and sellect a different row and perform the same task. If you can help it would be appreciated immensely. Nymrod

 

[My Aswer Is This]

We need to know:
1. The predefined columns and row number
2. The name of the sheets we are working with.
I would think you need to create another sheet and on that sheet say Column “A” and then put in the column numbers we are working with and the row number.

 

[Nymrod]

We need to know:
1. The predefined columns and row number
2. The name of the sheets we are working with.
I would think you need to create another sheet and on that sheet say Column “A” and then put in the column numbers we are working with and the row number.

Here is a copy of the Macro Code

Sub BookingIn()
'
' BookingIn Macro
'
' Keyboard Shortcut: Ctrl+o
'
Cells(Application.ActiveCell.Row, 2).Select

Selection.Copy
Sheets("Job Bag").Select
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Booking In").Select
Range("C4").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Job Bag").Select
Cells(Application.ActiveCell.Row, 3).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Booking In").Select
Range("D4").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Job Bag").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Booking In").Select
Range("E4").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Job Bag").Select
Range("H2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Booking In").Select
Range("F4").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Job Bag").Select
Range("C17").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Booking In").Select
Range("G4").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Job Bag").Select
Range("D9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Booking In").Select
Range("H4").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Job Bag").Select
Range("D10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Booking In").Select
Range("I4").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Job Bag").Select
Range("D11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Booking In").Select
Range("J4").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Job Bag").Select
Range("D26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Booking In").Select
Range("K4").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Job Bag").Select
Range("D12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Booking In").Select
Range("L4").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Job Bag").Select
Range("D13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Booking In").Select
Range("M4").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Job Bag").Select
Range("D14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Booking In").Select
Range("N4").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Job Bag").Select
Range("D27").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Booking In").Select
Range("P4").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Job Bag").Select
Range("H7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Booking In").Select
Range("Q4").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Job Bag").Select
Range("D24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Booking In").Select
Range("R4").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Job Bag").Select
Range("D25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Booking In").Select
Range("S4").Select
Application.CutCopyMode = False

Selection.Copy
Sheets("Job Bag").Select
Range("F4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[Nymrod]

I was hoping there was a way to set the ActiveRow in the booking on sheet once selected until the macro had finished running. Is this possible ?

 

[My Aswer Is This]

Nymrod.
I see what your doing and it took a lot of work to put this together but if you would allow me I could make the script a lot shorter.
It appears your just copying a lot of cells from one sheet to the other.
If you would just make a list like this for me I could work on this:


Copy Bookings.....Paste to Job Bags
C4............................D4
C4............................F4
D9...........................G4

Etc. Etc.

 

[Nymrod]

Appreciate your response, had a moment at work this afternoon that seemed to have solved the problem. Heres the final Macro

This line which moves position was in the wrong position in the macro. (Cells(Application.ActiveCell.Row, 4 or 5 or 6 etc).Select) throughout the macro

Sub BookingIn()
'
' BookingIn Macro
'
' Keyboard Shortcut: Ctrl+o
'
Cells(Application.ActiveCell.Row, 2).Select

Selection.Copy
Sheets("Job Bag").Select
Range("F2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Sheets("Booking In").Select
Cells(Application.ActiveCell.Row, 3).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Bag").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Booking In").Select
Cells(Application.ActiveCell.Row, 4).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Bag").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Booking In").Select
Cells(Application.ActiveCell.Row, 5).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Bag").Select
Range("H2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Booking In").Select
Cells(Application.ActiveCell.Row, 6).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Bag").Select
Range("C17").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Booking In").Select
Cells(Application.ActiveCell.Row, 7).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Bag").Select
Range("D9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Booking In").Select
Cells(Application.ActiveCell.Row, 8).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Bag").Select
Range("D10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Booking In").Select
Cells(Application.ActiveCell.Row, 9).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Bag").Select
Range("D11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Booking In").Select
Cells(Application.ActiveCell.Row, 10).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Bag").Select
Range("D26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Booking In").Select
Cells(Application.ActiveCell.Row, 11).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Bag").Select
Range("D12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Booking In").Select
Cells(Application.ActiveCell.Row, 12).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Bag").Select
Range("D13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Booking In").Select
Cells(Application.ActiveCell.Row, 13).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Bag").Select
Range("D14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Booking In").Select
Cells(Application.ActiveCell.Row, 14).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Bag").Select
Range("D27").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Booking In").Select
Cells(Application.ActiveCell.Row, 16).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Bag").Select
Range("H7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Booking In").Select
Cells(Application.ActiveCell.Row, 17).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Bag").Select
Range("D24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Booking In").Select
Cells(Application.ActiveCell.Row, 18).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Bag").Select
Range("D25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets("Booking In").Select
Cells(Application.ActiveCell.Row, 19).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Job Bag").Select
Range("F4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

its working well now thanks for your help

 

[My Aswer Is This]

I'm glad it's working for you now. If you would like to see a very simple solution to this script I could show you here in case you want to learn for the next time: Just complete the below and I could shorten this script to about 10 lines.

Copy Bookings.....Paste to Job Bags
C4............................D4
C4............................F4
D9...........................G4

Etc. Etc.