I am trying to copy info from one sheet into another using a macro. I'd like to be able to select a cell in Sheet A (eg A10), and using that as a reference copy cells in the same row, eg c10, d10 and g10, and paste that info into static cells in Sheet B, eg $A$6, $A$7, and $A$8. Have looked through the message boards but haven't found anything that uses the active cell, and multiple cells. Thanks in advance! Adrian
1 Answer
The task can be completed using simple statements like the following sample:
Worksheets("Sheet A").Range("A10").Copy destination:=Worksheets("Sheet B").Range("$A$6:$A$8")
In case you want be able to select a cell in Worksheets("Sheet A") and copy it to the destination Range (as shown above), then you may create a Sub like the following one:
Sub PasteSelection()
Selection.Copy Destination:=Worksheets("Sheet B").Range("$A$6:$A$8")
End Sub
and call it using, for example, ALT+F8 shortcut to run the Excel VBA macro.
Hope this may help.
10 Comments
feaseyweiss
Thanks for you quick reply! I am hoping to automate the cell selection (based on the active cell), and prevent entering the range for each time I copy certain cells across. Each time I will be copying the same cells (column) in a row, and the row will change with Sheet A for each new record, and it will always populate the same cells in sheet B, which I am using to create a pdf invoice, and save each time the macro is finished so I can email it out.
Alexander Bell
I would recommend you to clarify your question and add VBA code: you can use the macro recorder, then we would be able to help. Thanks and regards,
feaseyweiss
Application.CutCopyMode = False Selection.Copy Sheets("Invoice").Select Range("A6").Select ActiveSheet.Paste Sheets("Form responses 1").Select Range("D10").Select Application.CutCopyMode = False Selection.Copy Sheets("Invoice").Select Range("B6").Select ActiveSheet.Paste End Sub
Alexander Bell
The answer has been given to you pertinent to the cell range in your original question. You can apply it to any other cell range.
feaseyweiss
To clarify the q, I have a sheet with customer data. I want to select a row (corresponding to a customer) and then run a macro that takes value form A6 for example and populates sheet 2 (invoice), b11 (which is the first name), and tehn automatically takes the the value in cell c6 in sheet 1, and copies it into sheet 2, c12, and so on. Hope this helps
|