+ Reply to Thread
Results 1 to 4 of 4

Copy Paste Loop

Hybrid View

imaginekat Copy Paste Loop 06-15-2015, 12:49 PM
JOHN H. DAVIS Re: Copy Paste Loop 06-15-2015, 01:53 PM
imaginekat Re: Copy Paste Loop 06-15-2015, 02:14 PM
JOHN H. DAVIS Re: Copy Paste Loop 06-15-2015, 02:18 PM
  1. #1
    Registered User
    Join Date
    05-28-2015
    Location
    Houston, TX
    MS-Off Ver
    2010
    Posts
    7

    Copy Paste Loop

    Hello,
    Sorry for the basic question, but I am having a problem figuring out where I am going wrong in my code as a beginner.
    I have columns of data that I need copied into an active column ("Input") which is linked to a calculations worksheet ("Calculations") in the same workbook. I then need some of these values copied into a 3rd worksheet ("Percent Difference"). I need this procedure to repeat from columns G - CW and each time the data is copied into the 3rd worksheet, it needs to be in a new row. Any help would be greatly appreciated.

    Sub Sample()
    
        Range("G7:G50").Copy
        Range("F7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Sheets("Calculations").Select
        Range("B1").Copy
        Sheets("Percent Difference").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial _
             Paste:=xlPasteValues
        Application.CutCopyMode = False
        Sheets("Calculations").Select
        Range("D13").Copy
        Sheets("Percent Difference").Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial _
             Paste:=xlPasteValues
        Application.CutCopyMode = False
        Sheets("Calculations").Select
        Range("D14").Copy
        Sheets("Percent Difference").Range("C65536").End(xlUp).Offset(1, 0).PasteSpecial _
             Paste:=xlPasteValues
        Application.CutCopyMode = False
        Sheets("Calculations").Select
        Range("H13").Copy
        Sheets("Percent Difference").Range("D65536").End(xlUp).Offset(1, 0).PasteSpecial _
             Paste:=xlPasteValues
        Application.CutCopyMode = False
        Sheets("Calculations").Select
        Range("H14").Copy
        Sheets("Percent Difference").Range("E65536").End(xlUp).Offset(1, 0).PasteSpecial _
             Paste:=xlPasteValues
        Application.CutCopyMode = False
    
    End Sub
    So from here, I need it to return to the sheet "Input" and copy H7:H50 to F7:F50 and repeat the exact string of copy/paste above, then return for columns I-CW. But loops are still pretty foreign to me! I hope that makes sense...
    Thanks for reading!
    -K

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy Paste Loop

    Test on a copy first.

    Sub imaginekat()
    Dim i As Long
    With Sheets("Calculations")
    For i = 7 To 101
        .Range("F7").Resize(50).Value = .Range(Cells(7, i), Cells(7, 50)).Value
        .Range("B1").Copy Sheets("Percent Difference").Range("A" & Rows.Count).End(3)(2)
        .Range("D13").Copy Sheets("Percent Difference").Range("B" & Rows.Count).End(3)(2)
        .Range("D14").Copy Sheets("Percent Difference").Range("C" & Rows.Count).End(3)(2)
        .Range("H13").Copy Sheets("Percent Difference").Range("D" & Rows.Count).End(3)(2)
        .Range("H14").Copy Sheets("Percent Difference").Range("E" & Rows.Count).End(3)(2)
    Next i
    End With
    End Sub

  3. #3
    Registered User
    Join Date
    05-28-2015
    Location
    Houston, TX
    MS-Off Ver
    2010
    Posts
    7

    Re: Copy Paste Loop

    Thank you very much for your help John!

    I had to make a couple modifications as I was getting an error with the 'With' function and my formulas were being copied into the "Percent Difference" sheet instead of the values.

    Sub imaginekat()
        Dim i As Long
        For i = 7 To 101
            Sheets("Input").Range("F7:F50").Value = Sheets("Input").Range(Cells(7, i), Cells(50, i)).Value
            Sheets("Calculations").Range("B1").Copy
            Sheets("Percent Difference").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial _
                Paste:=xlPasteValues
            Application.CutCopyMode = False
            Sheets("Calculations").Range("D13").Copy
            Sheets("Percent Difference").Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial _
                Paste:=xlPasteValues
            Application.CutCopyMode = False
            Sheets("Calculations").Range("D14").Copy
            Sheets("Percent Difference").Range("C65536").End(xlUp).Offset(1, 0).PasteSpecial _
                Paste:=xlPasteValues
            Application.CutCopyMode = False
            Sheets("Calculations").Range("H13").Copy
            Sheets("Percent Difference").Range("D65536").End(xlUp).Offset(1, 0).PasteSpecial _
                Paste:=xlPasteValues
            Application.CutCopyMode = False
            Sheets("Calculations").Range("H14").Copy
            Sheets("Percent Difference").Range("E65536").End(xlUp).Offset(1, 0).PasteSpecial _
                Paste:=xlPasteValues
            Application.CutCopyMode = False
        Next i
    End Sub
    Once again, thank you very much for your help!

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy Paste Loop

    You're welcome. Glad to help out.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Destination copy and paste (values only) for copy loop
    By mr_mango81 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-01-2013, 08:59 PM
  2. [SOLVED] Copy Paste Loop ends before copy/paste is finished
    By brgr4u in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-08-2013, 04:01 PM
  3. [SOLVED] Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)
    By nixon72 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2013, 12:46 PM
  4. [SOLVED] VBA loop copy/paste
    By barrec in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-06-2012, 04:21 PM
  5. Excel 2007 : Next Row Loop - Copy Paste
    By GaidenFocus in forum Excel General
    Replies: 0
    Last Post: 11-04-2010, 10:31 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1