Conditionally copy selected cells in rows depending on cell contents.

  • Hello All, Running into some problems with Excel. My understanding of Macros is quite basic, but can read code at a basic level. This document is pulling information from multiple (10-15) schedules that contain all scheduling data for an event, and compiling just the Transport information. All current schedules are formatted the same, so the macro shouldn't need to change for each document. What I'm trying to do is copy the contents from specific cells in a row, to a new workbook, only if a cell in column F contains "GT". And then re-arrange the order of the rows in the new workbook. i.e. If Cell F24 contained "GT", then Copy C24(Date) to the next fully available row in A column in the new workbook, then copy D24(Start Time) to the same row that C24 went into, however this time in B column. Then copy E24(End Time) to the same row that C24 went into, however this time in C column. And so on and so forth, until there is no more data to read in each file. Any help that you could send my way would be greatly appreciated. Thanks in advance, Dan.

  • Re: Conditionally copy selected cells in rows depending on cell contents.


    Try this

  • Re: Conditionally copy selected cells in rows depending on cell contents.


    Thanks for your reply! I coded in everything that I needed, however I get a syntax error on "If InStr(Sheets(oldsheet).Range("F" & i).Value, "GT") 0 Then" Below is the full set of code that I'm using. Any ideas? Cheers, Dan.


  • Re: Conditionally copy selected cells in rows depending on cell contents.


    try








  • Re: Conditionally copy selected cells in rows depending on cell contents.


    This is where I'm up to. I get a 'Subscript out of range' debug error when the macro gets to this line:

    Code
    DataEnd = Sheets(oldsheet).Range("F1").End(xlDown).Row

    Could it have something to do with calling it 'Sheets(oldsheet)' when there is only one sheet in each workbook? Attached is the code that I'm working with. (I'm not sure how to post code without it all being bunched up like in my previous post.) Cheers, Dan.

  • Re: Conditionally copy selected cells in rows depending on cell contents.


    That's becuase all data is stored in different workbooks that only have one worksheet. Is there a way to get around this?

  • Re: Conditionally copy selected cells in rows depending on cell contents.


    _






  • Re: Conditionally copy selected cells in rows depending on cell contents.



    Hi Jindon,


    I've given this a shot, but it doesn't seem to work, and I'm also unsure how the code actually works. Is there a chance you might be able to walk me though it?


    Cheers,
    Dan.

  • Re: Conditionally copy selected cells in rows depending on cell contents.


    Quote from jindon;564493

    Doesn't explain your problem.


    How is it not working ?


    I get a "Run-time error '9': Subscript out of range" on the line:

    Code
    Set ws = Workbooks("Z:\Scheduling Trial\ST_Production Schedule.xls").SCHED(1)   '<-


    Here is the code that I'm currently working with:


  • Re: Conditionally copy selected cells in rows depending on cell contents.


    Danando,


    Take some time to read the Forum Rules!


    Cross posting without providing a link to the other thread is a violation of the Forum Rules. We prefer you do not cross post, but when doing so you are obligated to provide the link per your agreement to abide the rules of this forum. (All Excel forum have a similar rule about cross posting.)


    You cross posted here: Excel Help


    To understand why this practice is not acceptable, click on "Message to Cross Posters" in my signature and read that page in full.


    Future violation of the rule may result in automatic deleting or locking of your thread.


    After you have read the rules, send a PM acknowledging your agreement to abide by them and this thread will be reopened


    EDIT: PM received, thread re-opened

  • Re: Conditionally copy selected cells in rows depending on cell contents.


    Code
    Set ws = Workbooks("Z:\Scheduling Trial\ST_Production Schedule.xls").SCHED(1)



    If ST_Production Schedule.xls is open then change to

    Code
    Set ws = Workbooks("ST_Production Schedule.xls").SCHED(1)


    If not

    Code
    Set ws = Workbooks.Open("Z:\Scheduling Trial\ST_Production Schedule.xls").SCHED(1)


    Assuming SCHED(1) is equivalent to Sheets(1) in Eng ver.

  • Re: Conditionally copy selected cells in rows depending on cell contents.


    Hi Jindon,


    Thanks for your help.


    I'm now at the point where I run the Macro, ST_Production Schedule.xlsx opens, but then nothing gets copied over.
    Im not sure if it's the code that brings the data over into the new workbook, as I'm not sure how to decipher this part of the macro:

    Code
    For Each r In rng          If r.Value Like "*GT*" Then _          ws.Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 3).Value = r.Offset(, -3).Resize(, 3).Value


    One of the scedules has changed now the data starts in Row 4, and the Column that would contain "GT" is in Column B.
    So to confirm:
    If a cell in Column B contains "GT", then data from that row needs to be copied over to a new blank row in the new workbook.
    Column F to be copied to Column A
    Column G to be copied to Column B
    Column H to be copied to Column C
    Column J to be copied to Column E
    Column K to be copied to Column F
    Column N to be copied to Column L
    Column O to be copied to Column H


    Here is the code that I am currently working off:


    Thanks for your help.
    Dan.

  • Re: Conditionally copy selected cells in rows depending on cell contents.


    I'll give you the advice that when you ask the question in the forum, don't change the conditions until you test the code with the sample data.


    If the code runs properly with it, ask to change the conditions. (I'm not even feel good though)


    Otherwise, just wasting the time to me.


    Don't think you can alter the code for yourself.
    If you can, you are already able to write the code for yourself.(It is not like the formula system and even formula system differs)



  • Re: Conditionally copy selected cells in rows depending on cell contents.


    Sorry Jindon, the code won't change again.


    With the new code, I am getting a "Runtime error '438' Object doesnt support this property or method." on the line

    Code
    Set ws = Workbooks("ST_Production Schedule.xls").Sheet(1)


    Any Ideas?


    Dan.


  • Re: Conditionally copy selected cells in rows depending on cell contents.


    OK, corrected, but now when I run the macro, I get a "Object doesn't support this property or method" error, after ST_Production Schedule.xls is opened.


    Current Code:


    Dan.




    Quote from jindon;565011

    OOps typo


    should be

    Code
    Set ws = Workbooks("ST_Production Schedule.xls").Sheets(1)
  • Re: Conditionally copy selected cells in rows depending on cell contents.


    I'm not sure.
    I'm running 2011 in Australia and it's the english version.\


    SCHED is the name of the sheet within the workbook. Should that be Sheet1 within the macro?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!