Printable View
I would like to create a code in VBA excel which would allow me to copy a line of a file A to file B. I give more detail.
No Month Entry 1 Entry 2 ........ Enters
1 Jan-09
2 Feb-09
3-March 09
4 April-09
5-May 09
6 June-09
7 July-09
8-August 09
On the file I want a code that would allow me to copy an entire row of the column entry from 1 to N by month. That is if I select the month of March, it must automatically copy all the 3 entries 1 to N and then paste the file in XL B
Is this possible?
It is obviously possible, but there is one or two questions ...
1. The file B is it always the same, I mean, should we copy the following according to your choice, or the copy is made each time a new blank file.
2. The data to be copied, is N always the same value?
3. For fun, to indicate the month to copy, do you want on your worksheet file A or with a 'window'
Depending on this you can determine how to proceed.
1. The file B is still the same. So the copy is made on the same destination file.
2. Inputs 1, Input 2, ... N entries represent the columns that have values entered for each month.
3. For illustration, the month still to be identified in the macro. Once I chose the month of March, for example, I would have automatically copies the values of columns, entries 1, entries 2, ... N entries following the line number indicated by the month chosen in the code ..
I put an explanation on the lines of code, I called the file 'FileA' and 'FileB'. I have not renamed the sheets (I'm in GB). I considered that the entry of the month was to copy the file Sheet1 in cell O1 (to adapt according to your needs). Another important point, I considered that you open the FileB manually before launching the macro. I have to add that the macro pass red lines copied. This is certainly not the code more 'effective' but it has merit, I think, to be understandable, the goal being that you learn
Code:Sub CopyDataAccordingMonth()
'
Dim MyMonth As Integer
Dim NbRows As Integer
Dim NbCol As Integer
Dim i As Integer
MyMonth = Sheets("sheet1").Range("o1") 'takes the value entered in the cell o1 of FileA
NbRows = Application.Subtotal(3, Sheets("sheet1").Range("b:b")) '
counts the number of line
For i = 2 To NbRows 'Loop trail that lines the column months
If Sheets("sheet1").Cells(i, 2) = MyMonth Then 'checks if the month of the line=choice
NbCol = Application.Subtotal(3, Sheets("sheet1").Range(i & ":" & i))
Sheets("sheet1").Range(Cells(i, 3), Cells(i, NbCol)).Copy 'Copy the line in question, only column C in the last columns.
Windows("FileB.xls").Activate 'pass on the FileB
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues 'Paste on first blank line
Windows("FileA.xls").Activate 'repass on FileA
Rows(i).Font.ColorIndex = 3 'applies the color red on the copied
End If
Next i
End Sub
Thanks for the code. I have two questions.
1) I plan to replace the month when I would consider. If today I want to select, for example in March. It is located in o1 as in o9, or even 11. how?
2) If I want to adapt this code to select a cell that appears on the same line that should replace it? I guess nbcol.
Sorry, I guess that is the result of the lengthy meeting that I had but I'm more of everything. So I summarize:
1 month to be copied is indicated in a cell 'independent' of your lines, I advise you to keep a single cell this definition. You rewrite it each time. If you want another cell adapts "o1" by cell chosen (may be on another sheet)
Add a 2 ° cell on the same line ... all depends if it is a contiguous cell
Anyway NbCol the number of columns in the row from column C => C2 If completed at F2 NbCol reference 4
If you want to add column F must be added 1 to NbCol
If you want to add column B take the other element which is the info. In fact, using Cell to define, you must indicate in the order line number, then the column number, so to have the cell it should be noted B4 Cells (4.2).
I hope I was clear ...