# Excel copy & paste Macro for selected cells



## nice1chris (Sep 10, 2007)

Hi

This is very frustrating for me but probably very easy for someone with the know!

I use excel for accounts and enter the relevant details along a row; 1 row for each transaction. I want to be able to select specific cells from that row and paste them into an Excel invoice template i have.

I have used the macro record button to do this and it works fine except for one thing; it always copies the cells from the row i created the macro from. I would like it to copy the cells from the row i have selected. Hope this makes sense.

It obviously selects the cell (c4 etc) in the code but i want it to select based on the row of the cell selected if you know what i mean.

Here is the basic code i have

Sub CreateInvoice()
'
' CreateInvoice Macro
' Macro recorded 01/09/2007 by User
'
' Keyboard Shortcut: Ctrl+i
'
ChDir "X:\Accounting\Customer Accounts\Invoices"
Workbooks.Open Filename:= _
"X:\Accounting\Customer Accounts\Invoices\Blank Invoice.xls", UpdateLinks:=3
Windows("Accounts September 2007.xls").Activate
Range("B8").Select
Selection.Copy
Windows("Blank Invoice.xls").Activate
Range("B4").Select
ActiveSheet.Paste
Windows("Accounts September 2007.xls").Activate
Range("C8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Blank Invoice.xls").Activate
Range("B2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Windows("Accounts September 2007.xls").Activate
Range("D8").Select
Selection.Copy
Windows("Blank Invoice.xls").Activate
Range("B8").Select
ActiveSheet.Paste
Range("A12:A16").Select
Application.CutCopyMode = False
Selection.Copy
Range("D1216").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Accounts September 2007.xls").Activate
Range("J8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Blank Invoice.xls").Activate
Range("B21:C21").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("Accounts September 2007.xls").Activate
Range("K8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Blank Invoice.xls").Activate
Range("D21").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Any help would be great

Cheers :up:


----------



## Zack Barresse (Jul 25, 2004)

Can you post a sample of your file? A small, yet accurate sample, leaving out any personal information and blanketing it with mock data which resembles anything sensitive.

*Edit*: Btw, next time use the code tags when posting your code. It will turn out a lot better and easier to quote/use on the board.


----------



## nice1chris (Sep 10, 2007)

Okay, here is the file - i think!

Cheers


----------



## Zack Barresse (Jul 25, 2004)

Okay, some questions.

1) Can you post a sample of your "Blank Invoice.xls" file?
2) Why follow your Selection?

The second question is the more important of the two. We would generally want things to be as dynamic as possible. Leaving the human interaction of having the user pick a cell and run a procedure can raise errors and confusion. If you were to automate it - make the computer think for you - what exactly would go into that process? If you _needed_ user interaction, would you consider having a userform shown, which listed all of the data from the sheet?


----------



## nice1chris (Sep 10, 2007)

Hi 

I have attached blank invoice. I don't need anything major or sophisticated as i don't know that much about what excel can do. The macro selects the data from the cells (invoice number, date, account number, price) so long as the macro chooses the right row the cells should always be in the same column if you know what i mean.

The blank invoice use a vlookup to obtain address info when the account number is copied.

Cheers


----------



## nice1chris (Sep 10, 2007)

forgot to mention answer to your question

The accounts transaction would be created one at a time and then a macro would be run to turn the data into an invoice.

I wanted it so that when i click a cell on the appropriate row an invoice is created using its data.

Cheers


----------



## bomb #21 (Jul 1, 2005)

Then you actually need 3 things -- the invoice sheet, a customers table *and* a table of transactions.

Many here would say that Access ought to be used for this -- do you have it?


----------



## nice1chris (Sep 10, 2007)

Hi

everything is already set-up. I have a "Vlookup" table in excel with addresses, a blank invoice sheet to do all calculations and the transaction table (self calculating) all in excel. everything is fine as it is except the macro to automate it!

Maybe i don't explain it very well! It is possible i need a programming forum. 

I just want a macro to copy and paste. Then have it work on any row i choose!

Cheers


----------



## Rollin_Again (Sep 4, 2003)

You can use the built in row property to determine which cell to select.

Instead of using something like *Range("C3").Select* you can select the enter row or a single cell in that specific row and use the following

*Range("C" & Selection.Row).Select*

That should give you a start. The code generated by the macro record includes lots of extra unnecessary stuff that can be eliminated. For example you do not need to select the cells before copying them and you can specify the copy destination without having to select the destination sheet first. Try working with what I gave you and then post your final working code and we'll show you how to condense it and make it more efficient. I wouldn't worry too much about the efficiency of your macro since it is relatively simple. If you ever get into writing complex macros that work with thousands of rows of data you would want the code to be as clean and efficient as possible. Good luck!

Regards,
Rollin


----------



## nice1chris (Sep 10, 2007)

HI

Rollin_again that was exactly what i wanted. Worked a treat!!  

Because of the low volume of work the macro will do i only need it to a little. I don't need any sophisticated. Now once i enter the details on my accounts it auto generates an invoice.

Cheers - very happy!  

Whilst on the subject of excel is it possible to run a macro that saves using a filename taken from a cell. for example cell B4 contains 'invoice number' and B8 contains 'customer name' therefore saving a file called "b4value b8value invoice.xls"

If i get code for this i'll be bouncing off the walls!!

Cheers


----------



## Rollin_Again (Sep 4, 2003)

If the Workbook you want to save is the active workbook you can use the line below. Just change the *C:\* to the path of your choice.


```
ActiveWorkbook.SaveAs _
("C:\" & Range("B4").Value & " " & Range("B8").Value & " invoice" & ".xls")
```
If you want to save the workbook and it is not the active workbook you can use the code below. Just change the *C:\* to your specific path and change the workbook name and sheet names to your specific values.


```
Workbooks("Book1").SaveAs _
("C:\" & Workbooks("Book1").Sheets("Sheet1").Range("B4").Value _
& " " & Workbooks("Book1").Sheets("Sheet1").Range("B8").Value & " invoice" &".xls")
```
Regards,
Rollin


----------



## Zack Barresse (Jul 25, 2004)

If you would like more, I would advise to keep the template in the same workbook as a hidden sheet. I was going to put some work into it, but if you are satisfied, I will not continue. Let me know if you would like some work done on this. Take care.


----------



## nice1chris (Sep 10, 2007)

Great help folks

Cheers to everyone ... think that's me done on this one!

:up:


----------



## davesexcel (Aug 12, 2007)

Just an observation when looking at the code, the short cut key to run the code is ctrl-i
maybe ctrl-shift-i would be better as ctrl-i is already a microsoft function for italics


----------



## Zack Barresse (Jul 25, 2004)

Please mark this thread as Solved as well.


----------

