Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 16-04-2009
Member
 
Join Date: May 2008
Posts: 979
Copy and paste a conditional

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?
Reply With Quote
  #2  
Old 16-04-2009
XSI XSI is offline
Member
 
Join Date: May 2008
Posts: 271
Re: Copy and paste a conditional

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.
Reply With Quote
  #3  
Old 16-04-2009
Member
 
Join Date: May 2008
Posts: 979
Re: Copy and paste a conditional

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 ..
Reply With Quote
  #4  
Old 16-04-2009
XSI XSI is offline
Member
 
Join Date: May 2008
Posts: 271
Re: Copy and paste a conditional

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
Reply With Quote
  #5  
Old 16-04-2009
Member
 
Join Date: May 2008
Posts: 979
Re: Copy and paste a conditional

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.
Reply With Quote
  #6  
Old 16-04-2009
XSI XSI is offline
Member
 
Join Date: May 2008
Posts: 271
Re: Copy and paste a conditional

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 ...
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Copy and paste a conditional"
Thread Thread Starter Forum Replies Last Post
I cannot do Copy and Paste or Paste Special between Excel Workbooks Acca-OR Windows Software 5 26-04-2012 04:16 AM
copy& paste in facebook ramada90 Technology & Internet 2 24-07-2011 10:59 PM
Unable to copy and paste in mac NSA_CIA Operating Systems 3 17-11-2009 01:46 PM
Copy Text from One Cell to Another in Microsoft Excel without copy & paste Computer_Freak Tips & Tweaks 0 18-03-2009 10:00 PM
Copy and Paste does not work M.Scarlet Microsoft Project 1 06-02-2008 03:46 PM


All times are GMT +5.5. The time now is 07:33 AM.