Excel Macro Loop Through Range, Copy/paste (some complications)

Question

I have a range of data (C2:C100) in "sheet2". I want to copy, individually, paste into cell A2 of worksheet "Detailed LOC", copy results from (A2:K2) of "Detailed LOC", and paste (value only) into (A2:K2) for first value, (A3:K3) for second, and so on) of worksheet "All LOC". I've read that copy/paste are intensive, but the formulas in cells (A1:D1) in sheet2 are complicated, and I'm only wanting the values they produce in the resulting paste in "All LOC". I recorded the macro for what I want it to do, for the first cell in the range from "sheet2". I'm unsure of how to integrate the loop to get it repeat for the entire range of "sheet2", aswell as how to get it to paste the results in "ALL LOC" in descending rows. Note the step that takes place in "ALL LOC" always occurs in the 2nd row (A2:K2), not descending rows. Thanks for any help/advice.

Range("C2").Select
Selection.Copy
Sheets("Detailed LOC").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A2:K2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("All Loc").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Sheets("Sheet2").Select
Range("A1").Select

I suggest you have a look at [how to avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). And for your issue, I don't see why you need a loop if you don't have to arbitrate each row individually. A screenshot of your data might help making this more clear. — Display name, Feb 08 '19 at 00:14
Try to explain one, probably two whole 'rounds' of the process so people can understand what to loop and where to copy what, e.g.: 1. Copy "Sheet2 C2" to "Detailed LOC A2". Copy "Detailed LOC A2:K2" to "All LOC" A2. 2. Copy "Sheet2 C3" to "Detailed LOC" A3. Copy "Detailed LOC A3:K3" to "All LOC" A3 ... etc. This is probably wrong but you can see what I mean. What does the 'descending' part mean? — VBasic2008, Feb 08 '19 at 00:24
Yeah, couldn't make sense of this part "Note the step that takes place in "ALL LOC" always occurs in the 2nd row (A2:K2), not descending rows." but tried to give a solution looping through cells with the code of the macro. See below and leave comments so we can adapt it to fit your needs. — Ricardo Diaz, Feb 08 '19 at 00:27
For clarification: 'round1': Copy "Sheet2 C2" to "Detailed LOC A2". Copy values of "Detailed LOC A2:K2" to "All LOC A2:K2". 'round2': Copy "Sheet2 C3" to "Detailed LOC A2". Copy values of "Detailed LOC A2:K2 " to "All LOC A3:K3". The First copy goes down a range, say "Sheet2 C2:C100" but is always pasted in "Detailed LOC A2". The second copy is always "Detailed LOC A2:K2", but the paste -values only- needs to go to a new row each time on "All LOC", so "All LOC A2:K2", "All LOC A3:K3", All LOC A4:K4", and so on.. — Jonathan Keeling, Feb 09 '19 at 19:29

score 0 · Answer 1 · answered Feb 08 '19 at 00:22

Please give this a try and customize the code to fit your needs.

Backup your workbook before trying!

Sub CopyRanges()

    ' Declare objects
    Dim sourceRange As Range
    Dim detailedRange As Range
    Dim sourceCell As Range

    ' Declare other variables
    Dim sourceSheetName As String
    Dim detailedSheetName As String
    Dim allSheetName As String
    Dim sourceRangeAddress As String
    Dim counter As Integer ' Change for long if more than 32.000 items

    ' Initialize variables
    sourceSheetName = "Sheet2"
    detailedSheetName = "Detailed LOC"
    allSheetName = "All LOC"

    ' Define the address of the source range
    sourceRangeAddress = "C2:C100"

    ' Initialize the source range
    Set sourceRange = ThisWorkbook.Worksheets(sourceSheetName).Range(sourceRangeAddress)

    counter = 1

    ' Loop through each cell in source range
    For Each sourceCell In sourceRange

        ' Copy to detailed sheet (no selection - copy - paste!) - Change the column "A" if needed
        ThisWorkbook.Worksheets(detailedSheetName).Range("A" & sourceCell.Row).Value = sourceCell.Value

        ' Copy to all sheet (if you have formulas, this pastes the values. As you didn't mention, please confirm)  - Change the column "A and K" if needed
        ThisWorkbook.Worksheets(allSheetName).Range("A" & sourceCell.Row & ":" & "K" & sourceCell.Row).Value = ThisWorkbook.Worksheets(detailedSheetName).Range("A" & sourceCell.Row & ":" & "K" & sourceCell.Row).Value

        counter = counter + 1

    Next

    MsgBox "processed " & counter & " cells"

End Sub

Excel Macro Loop Through Range, Copy/paste (some complications)

1 Answers1