Copy/Paste Loop Macro

**danderson2692** · 06-14-2012, 12:00 PM

I am trying to create a macro to copy data from one sheet to another. The first sheet has data that has been entered and the second sheet is used organize the data so it can be used to make calculations and graphs from the data. The problem that I am having now is being able to loop through all of the lines of the entered data in order to copy it to the second sheet from which the data is used.
The data in "Enter Data" is in entries of 9 rows by 27 columns (A2:AA10) and the converted code ("Hr-by-Hr Chart Data") is 30 rows by 20+ columns. I am using column B, first row of each data entry in Enter Data, and column A, first empty row in Converted Data, as the baseline boxes from which ranges are selected relative to because there will always be data for the cells in those columns, while there may or may not be data in most of the rest... if that all makes sense.
Here is the declaring and looping parts of my code:

Sub Import_All_Raw_Data()

Sheets("Chart Data Updating").Visible = True
'This macro is used to take all data from the entered data sheet and converts
'it so it can be imported to the hour-by-hour sheet. You can filter and assess
'all data after it has been imported.
    Sheets("Hr-by-Hr Chart Data").Range("A7:A60000,D7:E60000,H7:I60000,K7:U60000").ClearContents
    Sheets("Enter Data").Select
    'UseRow is the variable for row to use in loop. OffsetRow will move down 9 rows after looping through each time.
    Dim OffsetRow As Integer
    Dim UseRow As Range
    OffsetRow = 0
    UseRow = Sheets("Enter Data").Range("B2")
        'Loop will take all data from the "Enter Data" page and move it to "Hr-by-Hr Chart Data" in the correct format until all
        'rows of "Enter Data" have been transfered.
         Do Until IsEmpty(UseRow)
            Set FRow = Sheets("Enter Data").Range("B2")
            Set UseRow = FRow.Offset(OffsetRow, 0)
            'Variable for first blank in date column
            Set Hourly = Sheets("Hr-by-Hr Chart Data").Range("A7").End(xlDown).Offset(1, 0)
            ' Insert Date
            UseRow.Copy
            Range("Hourly:Hourly.Offset(23,0)").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                   :=False, Transpose:=False

            'Other code here

            ' Step down 9 rows from current location in Enter Data
            OffsetRow = OffsetRow + 9
        Loop
Sheets("Chart Data Updating").Visible = False

End Sub

I have only been working with excel for a few weeks so I am still trying to learn the code and how to do relatively simple things like using variables correctly and looping correctly, so please let me know if I am doing anything that I really shouldn't do or if there is a better way to do it.

**AlphaFrog** · 06-14-2012, 02:01 PM

I'm not sure I follow what you want to achieve. Perhaps this will give you some ideas.

Sub Import_All_Raw_Data2()
    
''''This macro is used to take all data from the entered data sheet and converts
''''it so it can be imported to the hour-by-hour sheet. You can filter and assess
''''all data after it has been imported.
    
    Dim wsData  As Worksheet
    Dim wsHbH   As Worksheet
    Dim Lastrow As Long
    Dim Nextrow As Long
    Dim r       As Long
    
    Set wsData = Sheets("Enter Data")           'Source worksheet
    Set wsHbH = Sheets("Hr-by-Hr Chart Data")   'Destination worksheet
    
    wsHbH.Range("A7:A60000,D7:E60000,H7:I60000,K7:U60000").ClearContents
    
    Lastrow = wsData.Range("A" & Rows.Count).End(xlUp).Row  'Last used row on Source worksheet
    Nextrow = 7                                             'Next available row on the destination worksheet
    
    For r = 2 To Lastrow Step 9 'Loop from row 2 to the Lastrow every 9th row
    
        ' Copy from the source worksheet every 9th row in column B
        ' to groups of 23 cells in column A on the destination sheet
        wsHbH.Range("A" & Nextrow).Resize(23).Value = wsData.Range("B" & r).Value
        Nextrow = Nextrow + 23
        
        'Other code here
        
    Next r
    
End Sub

**danderson2692** · 06-14-2012, 02:28 PM

That is very good. I was not sure what would be the best way to set up the loop.
Just out of curiosity, why would you choose to use a 'for' loop instead of a 'do until isempty' loop?

**AlphaFrog** · 06-14-2012, 04:14 PM

You're welcome.

Originally Posted by danderson2692

...why would you choose to use a 'for' loop instead of a 'do until isempty' loop?

No major reason. Personal preference I guess. A For\Next loop does allow for a Step size in the one line of code.

Your original code was pretty good. The actual "Loop" wasn't much of an issue. Most improvements in the new code were in how Sheets and Ranges wre referenced.