PDA

View Full Version : Excel visual basic (ctrl+click) copying clicked cells to new excell file



0MM0
05-10-2013, 03:31 AM
On my job I am every day dealing with one problem. Because of that I need solutioN for doing it automaticly.

Description:

When I open Excel document there is a lot of cells from A1,A2, ...

I want next: when I (CTRL+mouse click) on 7 different places randomly choosen, I want to VB software remember order of selected cells from 1-7. Then I want to copy those cells to a new excel file named "L1".

You can see example of exc. document where are thoose 7 cells that I want to copy.

postimg.org/image/dexdw08df/

New excell file looks like on next picture ( NEW second file is not empty, it has before created base like on picture BELOW). and 7 selected cells have to be coppied always on positions: A14, C14, D14, F14, A6, I1.

postimg.org/image/i3b1es8e9/

Every time when I select another 7 cells and run VB code it has to be created a new excell file in specified folder lik on picture below

postimg.org/image/5qn3wmn43/

I hope somoeone will help me to solve this problem. Be free to ask me additional questions.

Sincerly,

MM

p45cal
05-12-2013, 10:57 AM
and 7 selected cells have to be copied always on positions: A14, C14, D14, F14, A6, I1.There are only 6 destination cells for 7 source cells. Where's the 7th (I5)?
While awaiting a response, something like:Sub blah()
DestAry = Array("A14", "C14", "D14", "F14", "A6", "I1")
Set xx = Selection
For fil = 1 To 4
fName = "L" & fil & ".xlsx" 'this assumes files are in same folder as current path (CurDir).
Set NewFil = Workbooks.Open(fName)
i = 0
For Each cll In xx.Cells
cll.Copy NewFil.Sheets("Sheet1").Range(DestAry(i))
i = i + 1
Next cll
NewFil.Close True
Next fil
End Sub
You would run the macro AFTER selecting the 7 cells in the appropriate order.
Is there a fixed number of files and are they always named L1, L2 etc.?
We could use another method to go through the files:
For each myfile in Array("L1","L2","L3","L4","X3","Sw23","MM01")
Workbooks.open(myfile)
..
..
next myFile

p45cal
05-12-2013, 01:40 PM
For others: cross post:
http://www.mrexcel.com/forum/excel-questions/701921-excel-visual-basic-ctrl-click-copying-clicked-cells-new-excell-file.html