Results 1 to 6 of 6

Thread: Copy and paste a conditional

  1. #1
    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?

  2. #2
    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.

  3. #3
    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 ..

  4. #4
    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

  5. #5
    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.

  6. #6
    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 ...

Similar Threads

  1. Replies: 5
    Last Post: 26-04-2012, 04:16 AM
  2. copy& paste in facebook
    By ramada90 in forum Technology & Internet
    Replies: 2
    Last Post: 24-07-2011, 10:59 PM
  3. Unable to copy and paste in mac
    By NSA_CIA in forum Operating Systems
    Replies: 3
    Last Post: 17-11-2009, 01:46 PM
  4. Replies: 0
    Last Post: 18-03-2009, 10:00 PM
  5. Copy and Paste does not work
    By M.Scarlet in forum Microsoft Project
    Replies: 1
    Last Post: 06-02-2008, 03:46 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,649,539.14473 seconds with 17 queries