Copy from cells with formulas to other cells

Microsoft Office excel 2007 - upgrade
November 22, 2010 at 07:17:12
Specs: Windows XP
Hello.

I'm no quite familiar with macros in Excel. But here's what I've got. I've got 2 cells with simple sum() formulas. These 2 cells contain 2 different prices. And then, after Excel calculates them, I have to copy these 2 values to 2 cells in 2 different columns in another sheet. And here's the problem: this sheet contains a list of products and the list is usually about a thousand titles long. So what I have to do is more than thousand times press Ctrl-C and Ctrl-V to copy the values (calculated prices) from formula cells to the cells of a certain product. Prices differ so I really have to copy-paste them.

And the question is: is there a way to automize this? Say, to have values automatically copied from the first 2 cells to the cells in the products sheet (so it's like one by one in a column or smth). A formula that I do not know or a macro?

Thanks!


See More: Copy from cells with formulas to other cells

Report •

#1
November 22, 2010 at 09:41:05
Would it possible to post a small example of your data - before and after - to help us understand your goal?

Please read the How-To referenced below before you post your data.

Thanks!

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
November 22, 2010 at 11:47:11
Ok, I'll just post a screenshot:
http://ipicture.ru/uploads/20101122...

See these 2 values, D6 and F6? Here's the deal. I look up some numbers in our company's catologs, add them in all those cells you can see in Sheet1 and get calculated prices (D6 and F6). Then I simply need to paste them in Sheet2 in the corresponding cells. And then again. And again. Look up - calculate - paste, look up - calculate - paste... So is there really a way to somehow make this irritating copy-paste automatic? Thanks.


Report •

#3
November 25, 2010 at 19:47:48
I guess I should try and explain it better. I need:
1. Copy value from one cell (the value, not the formula) in Sheet1
2. Find the next empty cell in a column in another sheet, say Sheet2
3. Paste the copied value from Sheet1 into that empty cell.
4. Repeat many times

Thanks.


Report •

Related Solutions

#4
November 25, 2010 at 21:44:54
You could trigger a Worksheet_Change macro based on a change in a specific cell.

You could trigger a Worksheet_SelectionChange macro based on the selection of a specific cell.

You could trigger a macro by clicking an Icon in a toolbar.

For example, if you always change a specific cell just prior to having to copy the data to Sheet 2, let's say B6, then the following code would copy Sheet 1 D6 and F6 to the next empty cell in Sheet 2 Columns K & M, respectively, as soon as you changed B6.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$6" Then
    nxtRw = Sheets(2).Range("K" & Rows.Count).End(xlUp).Row + 1
      Range("D6").Copy
        Sheets(2).Range("K" & nxtRw).PasteSpecial Paste:=xlValues
      Range("F6").Copy
        Sheets(2).Range("M" & nxtRw).PasteSpecial Paste:=xlValues
   Application.CutCopyMode = False
  End If
End Sub

This could be made a bit less automatic, by having it fire when you select a specific, D6. That would allow you change your cells in whatever order you want, then just click on D6 to make the copy happen.

Private Sub Worksheet_selectionChange(ByVal Target As Range)
  If Target.Address = "$D$6" Then
   Application.ScreenUpdating = False
    nxtRw = Sheets(2).Range("K" & Rows.Count).End(xlUp).Row + 1
      Range("D6").Copy
        Sheets(2).Range("K" & nxtRw).PasteSpecial Paste:=xlValues
      Range("F6").Copy
        Sheets(2).Range("M" & nxtRw).PasteSpecial Paste:=xlValues
   Application.CutCopyMode = False
   Application.ScreenUpdating = True
  End If
End Sub

Or you could make it "manual" by attaching this code to a button or shape. Click the shape and the copy would occur.

Sub CopyD6F6()
   Application.ScreenUpdating = False
    nxtRw = Sheets(2).Range("K" & Rows.Count).End(xlUp).Row + 1
      Sheets(1).Range("D6").Copy
        Sheets(2).Range("K" & nxtRw).PasteSpecial Paste:=xlValues
      Sheets(1).Range("F6").Copy
        Sheets(2).Range("M" & nxtRw).PasteSpecial Paste:=xlValues
   Application.CutCopyMode = False
   Application.ScreenUpdating = True
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Ask Question