Consulting

Results 1 to 7 of 7

Thread: Copy & Paste data from column I

  1. #1
    VBAX Tutor
    Joined
    Aug 2007
    Posts
    294
    Location

    Copy & Paste data from column I

    See attached workbook.......Vol -1 .xls

    Hi

    Been trying to copy the data from column I worksheet"Deal Selection" and add it to worksheet "Volume Summary" using the same functionality as the macro in module 1.

    But this pasting the data into the demand section as highlighted in green..

  2. #2
    VBAX Tutor
    Joined
    Aug 2007
    Posts
    294
    Location
    The origianl code was so excellent done by xld

  3. #3
    VBAX Tutor
    Joined
    Aug 2007
    Posts
    294
    Location
    Being try to do this my self. Cannot work out which part of the code will copy column I and paste it.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,140
    Location
    [vba]

    Sub Process_Supply()
    Dim i As Long
    Dim LastRow As Long
    Dim Numrows As Long

    With Application

    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    End With

    With Worksheets("Deal Selection")

    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    Numrows = LastRow - 8
    End With

    Call CopyData("Supply", "Obligations", Numrows)
    Call CopyData("Supply", "Actual Allocations", Numrows)
    Call CopyData("Demand", "Surplus/Shortages", Numrows)

    With Application

    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    End With
    End Sub

    Private Sub CopyData(ByVal SectionFrom As String, ByVal SectionTo As String, ByVal Numrows As Long)
    Dim SalesRow As Long
    Dim TargetRows As Long
    Dim SourceCell As Range
    Dim BaseCell As Range
    Dim cell As Range

    With Worksheets("Deal Selection")

    Set SourceCell = .UsedRange.Find(SectionFrom)
    If SourceCell Is Nothing Then

    MsgBox "Problem with data to be copied"
    Exit Sub
    End If
    End With

    With Worksheets("Volume Summary")

    Set cell = .Columns(2).Find(SectionTo)
    If Not cell Is Nothing Then

    Set BaseCell = cell.Offset(5, 0)
    Set cell = .Columns(2).Find(What:="Sale Contracts", after:=cell)
    If Not cell Is Nothing Then

    SalesRow = cell.Row
    TargetRows = SalesRow - BaseCell.Row - 2
    If TargetRows < Numrows Then

    .Rows(BaseCell.Row + 1).Resize(Numrows - TargetRows).Insert
    ElseIf TargetRows > Numrows Then

    .Rows(BaseCell.Row + 1).Resize(TargetRows - Numrows).Delete
    End If

    SourceCell.Offset(3, 0).Resize(Numrows).Copy BaseCell
    With BaseCell.Resize(Numrows).Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlMedium
    .ColorIndex = xlAutomatic
    End With
    End If
    End If
    End With

    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Tutor
    Joined
    Aug 2007
    Posts
    294
    Location
    Hi xld

    Firstly, apologies if this is a rude question. Does this copy column I from worksheet "Deal Selection"????

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,140
    Location
    Try it and see.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Tutor
    Joined
    Aug 2007
    Posts
    294
    Location
    It does....like i said apologies......but it is posting the end result in the wrong row in column B worksheet "Volume Summary"......

    The end results need to be in row 26, 62 and 98 column B worksheet "Volume Summary" these are the starting point fror the demand clients

Posting Permissions

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