+ Reply to Thread
Results 1 to 3 of 3

Calculate,Copy, Paste Cell Group Macro Adjustments

  1. #1

    Calculate,Copy, Paste Cell Group Macro Adjustments

    Tom Ogilvy was so kind to help me with the below macro which works
    great for copy and paste 5 number workbook but having a problem
    applying it to 6 number and 4 number workbook I was hoping to make
    changes to the macro so i could use it to
    copy and paste in workbooks with 6 numbers and another workbook with 4
    numbers so it would run the same way but for some reason it will not
    work.

    here are the changes i made to Tom's macro thinking it would run in
    the 6 number workbook but does not run
    Dim cell As Range, n As Long, v(1 To ""6"") As String
    v(4) = "AI": v(5) = "AJ" "": v(6) = "AK" ""
    could you please tell me what i am missing to make it run....thanks

    Here is Toms macro that works fine with 5 number workbook
    Sub afpaste()
    Dim rng As Range, i As Long, j As Long
    Dim cell As Range, n As Long, v(1 To 5) As String
    Dim s As Long
    s = Application.Calculation
    Application.Calculation = xlManual
    n = 25
    Set rng = Range("AF78:AJ78")
    v(1) = "AF": v(2) = "AG": v(3) = "AH"
    v(4) = "AI": v(5) = "AJ"
    For i = 1 To n
    Application.Calculate
    j = 0
    For Each cell In rng
    j = j + 1
    Cells(i + 80, v(j)).Value = cell.Value
    Next
    Next
    Application.Calculation = s
    End Sub


  2. #2
    Tom Ogilvy
    Guest

    Re: Calculate,Copy, Paste Cell Group Macro Adjustments

    Sub afpaste6()
    Dim rng As Range, i As Long, j As Long
    Dim cell As Range, n As Long, v(1 To 6) As String
    Dim s As Long
    s = Application.Calculation
    Application.Calculation = xlManual
    n = 25
    Set rng = Range("AF78:AK78")
    v(1) = "AF": v(2) = "AG": v(3) = "AH"
    v(4) = "AI": v(5) = "AJ" : v(6) = "AK"
    For i = 1 To n
    Application.Calculate
    j = 0
    For Each cell In rng
    j = j + 1
    Cells(i + 80, v(j)).Value = cell.Value
    Next
    Next
    Application.Calculation = s
    End Sub

    ------------

    Sub afpaste4()
    Dim rng As Range, i As Long, j As Long
    Dim cell As Range, n As Long, v(1 To 4) As String
    Dim s As Long
    s = Application.Calculation
    Application.Calculation = xlManual
    n = 25
    Set rng = Range("AF78:AI78")
    v(1) = "AF": v(2) = "AG": v(3) = "AH"
    v(4) = "AI"
    For i = 1 To n
    Application.Calculate
    j = 0
    For Each cell In rng
    j = j + 1
    Cells(i + 80, v(j)).Value = cell.Value
    Next
    Next
    Application.Calculation = s
    End Sub

    --
    Regards,
    Tom Ogilvy


    <[email protected]> wrote in message
    news:[email protected]...
    > Tom Ogilvy was so kind to help me with the below macro which works
    > great for copy and paste 5 number workbook but having a problem
    > applying it to 6 number and 4 number workbook I was hoping to make
    > changes to the macro so i could use it to
    > copy and paste in workbooks with 6 numbers and another workbook with 4
    > numbers so it would run the same way but for some reason it will not
    > work.
    >
    > here are the changes i made to Tom's macro thinking it would run in
    > the 6 number workbook but does not run
    > Dim cell As Range, n As Long, v(1 To ""6"") As String
    > v(4) = "AI": v(5) = "AJ" "": v(6) = "AK" ""
    > could you please tell me what i am missing to make it run....thanks
    >
    > Here is Toms macro that works fine with 5 number workbook
    > Sub afpaste()
    > Dim rng As Range, i As Long, j As Long
    > Dim cell As Range, n As Long, v(1 To 5) As String
    > Dim s As Long
    > s = Application.Calculation
    > Application.Calculation = xlManual
    > n = 25
    > Set rng = Range("AF78:AJ78")
    > v(1) = "AF": v(2) = "AG": v(3) = "AH"
    > v(4) = "AI": v(5) = "AJ"
    > For i = 1 To n
    > Application.Calculate
    > j = 0
    > For Each cell In rng
    > j = j + 1
    > Cells(i + 80, v(j)).Value = cell.Value
    > Next
    > Next
    > Application.Calculation = s
    > End Sub
    >




  3. #3
    Ken Johnson
    Guest

    Re: Calculate,Copy, Paste Cell Group Macro Adjustments

    Hi,
    I guessing....

    Sub afpaste4()
    Dim rng As Range, i As Long, j As Long
    Dim cell As Range, n As Long, v(1 To 4) As String
    Dim s As Long
    s = Application.Calculation
    Application.Calculation = xlManual
    n = 25
    Set rng = Range("AF78:AI78")
    v(1) = "AF": v(2) = "AG": v(3) = "AH"
    v(4) = "AI"
    For i = 1 To n
    Application.Calculate
    j = 0
    For Each cell In rng
    j = j + 1
    Cells(i + 80, v(j)).Value = cell.Value
    Next
    Next
    Application.Calculation = s
    End Sub

    for 4 number workbook, and...

    Sub afpaste6()
    Dim rng As Range, i As Long, j As Long
    Dim cell As Range, n As Long, v(1 To 6) As String
    Dim s As Long
    s = Application.Calculation
    Application.Calculation = xlManual
    n = 25
    Set rng = Range("AF78:AK78")
    v(1) = "AF": v(2) = "AG": v(3) = "AH"
    v(4) = "AI": v(5) = "AJ": v(6) = "AK"
    For i = 1 To n
    Application.Calculate
    j = 0
    For Each cell In rng
    j = j + 1
    Cells(i + 80, v(j)).Value = cell.Value
    Next
    Next
    Application.Calculation = s
    End Sub

    for 6 number workbook.

    Ken Johnson


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1