Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Location
    Annapolis, Maryland, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    insert text from one sheet to another (2002)

    I'm using Excel to create a schedule of many projects. Tasks are listed week-by-week. I want to set up a second sheet that shows tasks across projects for each week.

    My first question regarding this is, How do I get the text in one cell to be repeated in a cell on a different sheet?

    That's probably pretty easy for more experienced Excel users. If you have an answer only for that question, I'd love to hear it.

    My second question, I think, is a doozy. How do I create a macro that will:

    1. copy the first 7 cells of a row in Sheet 1 (this is the week)
    2. Paste those cells into a row in Sheet 2
    3. Find cells in that row in Sheet 1 that contain text and copy them
    4. Paste that text into the 9th column, beginning with the row where the week begins, and each subsequent task in subsequent columns
    5. Somehow include the project that each task is from, which is in the first row of the column of each respective task
    6. Repeat that process for each week.

    I've attached a sample file, which has my projects. But the new Managing Editor wants to use if for all projects in the department, which means adding a whole lot of projects. I started creating a weekly schedule in Week 2, but nothing is automated. It was done by some pretty tedious copying and pasting.

    I've been impressed over and over by the knowledge lurking in Woody's Lounge, and at the extent that people are willing and able to help. Thanks to anyone who's given feedback to me or anyone else in the past!
    Michael Coleman
    BOMI International

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insert text from one sheet to another (2002)

    Question 1 is fairly simple. In Sheet2 cell I3 in your workbook, you could put the following formula to get the text from Sheet1 cell J1:

    <pre>=Sheet1!$J$1
    </pre>


    The second question is a bit more complicated and requires some additional information.

    1- Where do you want to start copying from to create sheet 2? Row 4? The first visible row after row 3? Something else?

    2- How far do you want to copy? One week? Two weeks? One month? To the end of the year? To the end of the data?

    3- Why is "Style manual" in O14 on Sheet2?

    4- Are those squares (for example in cell H3 on Sheet3 there for some reason, or are they just decoration?
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Location
    Annapolis, Maryland, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insert text from one sheet to another (2002)

    1- Where do you want to start copying from to create sheet 2? Row 4? The first visible row after row 3? Something else?
    Start copying from the first visible row after Row 5
    Start copying to Row 4

    2- How far do you want to copy? One week? Two weeks? One month? To the end of the year? To the end of the data?
    To the end of the data in any column beyond Column G. If the check boxes foul that up, I can replace them with shading and borders.

    3- Why is "Style manual" in O14 on Sheet2?
    I was copying and pasting in chunks, and missed deleting that one.

    4- Are those squares (for example in cell H3 on Sheet3 there for some reason, or are they just decoration?
    They're check boxes. As I said above, I can get rid of these if necessary.
    Michael Coleman
    BOMI International

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insert text from one sheet to another (2002)

    OK, I think that gives me enough to get started. It is going to take a while, so I will look at it later this evening.
    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insert text from one sheet to another (2002)

    OK, I had some time to play with this. I think that the macro below does what you want. I have also attached your workbook with the macro included which I had to ZIP because it is too large with the macro to upload. Some of this code may look very strange because you have merged cells in the sheets.

    <pre>Public Sub CopyToTasks()
    Dim lLastRow As Long, lFirstRow As Long, I As Long, J As Long, K As Long
    Dim oSrc As Worksheet, oTgt As Worksheet
    Application.ScreenUpdating = False
    Set oSrc = Worksheets("Sheet1")
    Set oTgt = Worksheets("Sheet2")
    lLastRow = Worksheets("Sheet1").UsedRange.Row + Worksheets("Sheet1").UsedRange.Rows.Count - 7
    For lFirstRow = 0 To lLastRow
    If oSrc.Range("A6").Offset(lFirstRow, 0).EntireRow.Hidden = False Then
    Exit For
    End If
    Next lFirstRow
    If lFirstRow >= lLastRow Then
    Exit Sub
    End If
    oSrc.Select
    oTgt.Cells.MergeCells = False
    oTgt.Range("A2") = ""
    oTgt.Range("A3", "IV65535").Clear
    K = 0
    I = lFirstRow
    Do While I < lLastRow
    oTgt.Range("A2").Offset(K, 0).Value = oSrc.Range("A6").Offset(I, 0).Value
    oTgt.Range("A2").Offset(K, 0).NumberFormat = "mmmm yyyy;@"
    oTgt.Range("A2").Offset(K, 0).HorizontalAlignment = xlCenter
    oTgt.Range("A2").Offset(K, 0).Font.Bold = True
    oTgt.Select
    oTgt.Range(Range("A1").Offset(K + 1, 0), Range("A1").Offset(K + 1, 6)).MergeCells = True
    K = K + 1
    I = I + 2
    Do While Trim(oSrc.Range("A6").Offset(I, 0).Value & oSrc.Range("G6").Offset(I, 0).Value) <> ""
    oSrc.Select
    oSrc.Range(Range("A6").Offset(I, 0), Range("G6").Offset(I, 0)).Copy
    oTgt.Paste Destination:=oTgt.Range("A2").Offset(K, 0)
    For J = 9 To 42 Step 3
    If oSrc.Range("A7").Offset(I - 1, J).Value <> "" Then
    oTgt.Range("I2").Offset(K, 0).Value = oSrc.Range("A1").Offset(0, J).Value
    oTgt.Range("J2").Offset(K, 0).Value = oSrc.Range("A7").Offset(I - 1, J).Value
    K = K + 1
    End If
    Next J
    If (Trim(oTgt.Range("A2").Offset(K, 0).Value & oTgt.Range("G2").Offset(K, 0).Value) <> "") Then
    K = K + 1
    End If
    I = I + 1
    Loop
    I = I + 1
    K = K + 1
    Loop
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    Feb 2003
    Location
    Annapolis, Maryland, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insert text from one sheet to another (2002)

    It worked just fine. Thanks!
    Michael Coleman
    BOMI International

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insert text from one sheet to another (2002)

    Glad we could solve your problem.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •