Copying active cells from one sheet to another

[McKnight05]

Hello everyone,

sorry if this question was asked before - I have searched through the forums and haven't found anything similar.

So this is what I'm trying to do:

I want to have a button on SHEET01 that (when pressed) will copy the values from the currently selected cells on SHEET01 to the corresponding cells on SHEET02. This needs to work in both cases: if only one cell on Sheet01 is selected and if a whole range of cells (rows, columns..) are selected.

How would the VBA code for this look like?

Any help is appreciated.

Regards,
McKnight05

 

[NewOrderFac33]

Welcome to the forum!

How about

Sub CopyFromOneToTwo()
    Dim MyAddress As String
    MyAddress = Selection.Address
    Sheets("Sheet2").Range(MyAddress).Value = Sheets("Sheet1").Range(MyAddress).Value
End Sub

or even shorter

Sub CopySelectionToAnotherSheet()
    Sheets("Sheet2").Range(Selection.Address).Value = Sheets("Sheet1").Range(Selection.Address).Value
End Sub

 

[McKnight05]

Hello Pete and thank you. That is exactly what I needed and your code has tough me quite a bit actually - since I am a complete newbie to working in Excel with VBA.

I got another predicament if you care to to try, whenever you can, no rush:

I want my entire selection from Sheet01 to be copied to Sheet02 BUT ... not exactly as it was on Sheet01. I want to be able to specify where each cell from my Sheet01 selection is copied to my Sheet02 in the code and extend it according to my situation.

Example:

1. I select a range from B5 to E5
2. I run the code
3. Cell Sheet01/B5 is copied to Sheet02/A5 .... cell Sheet01/C2 is copied to Sheet02/G58 .... cell Sheet01/D2 is copied to Sheet02/P12 .... cell Sheet01/E5 is ignored


My pseudo-code for this situation:

Determine/fetch the range (addresses) of selected cells
Copy first cell from the range to a static location A
Copy second cell from the range to a static location B
Copy third cell from the range to static location C
//Forth cell from the range is ignored because only 3 cell "transport" are defined in the code

Does this make any sense to you?
Is it possible for the code to work if multiple rows and columns are selected. For example, the code iterates through all cells in the first selected row then moves down to the next row and so on and so on.

Regards,
Gordon

 

[DeusXv]

This is a very simple solution to the task at hand, but it's limited to the fact that it only works for a set range and you need to add more code if you were copying more cells than the range B2-E5. I just added the lines to Pete's code and that should do what you asked

Sub CopyFromOneToTwo()
    Dim MyAddress As String
    MyAddress = Selection.Address
    Sheets("Sheet2").Range(MyAddress).Value = Sheets("Sheet1").Range(MyAddress).Value
    Sheets("Sheet2").Range("C2:C2").Cut Destination:=Sheets("Sheet2").Range("G58:G58")
    Sheets("Sheet2").Range("D2:D2").Cut Destination:=Sheets("Sheet2").Range("P12:P12")
    
End Sub

The above will do what you asked, but if you select B2-F5, cell F5 will not be moved anywhere as there is no code for it and you would have to add a line like this to the code

Sheets("Sheet2").Range("F2:F2").Cut Destination:=Sheets("Sheet2").Range("H23:H23")

This should do the trick until one of the lads posts a better solution

 

[NewOrderFac33]

Hi, Gordon,

Does this meet your needs:

Sub SplitAndCopySelection()


    Dim MyCell As Range
    Dim FirstAddress As String
    Dim SecondAddress As String
    Dim ThirdAddress As String
    
    FirstAddress = Selection.Cells(1).Address
    SecondAddress = Selection.Cells(2).Address
    ThirdAddress = Selection.Cells(3).Address
        
    'Change target addresses to suit
    Sheets("Sheet2").Range("B2").Value = Sheets("Sheet1").Range(FirstAddress).Value
    Sheets("Sheet2").Range("C2").Value = Sheets("Sheet1").Range(SecondAddress).Value
    Sheets("Sheet2").Range("D2").Value = Sheets("Sheet1").Range(ThirdAddress).Value


End Sub

And Deus, in all this time, I'd never thought to use ".Cut" in the way you have, so thanks for that!

 

[DeusXv]

Hi, Gordon,

And Deus, in all this time, I'd never thought to use ".Cut" in the way you have, so thanks for that!

The .Cut is my go to function is my go to one when I need to move cell values

I knew you would have a cool way of doing it too, the way you selected the range and moved them was class as well I have done it other ways but it as more complicated and just ended up doing my head in, so I will definitely steal your way of doing it from now on

 

[NewOrderFac33]

You're too kind!