Copy and paste loop between sheets

[ifu06416]

Could anyone give me a hand with this code?

It copying once but it wont loop down the page

Sub macro222()
Sheets("Data").Activate
Set myR = Range("B12")
i = 0
Do Until myR.Offset(i, 0).Value = ""
myR.Offset((i + 2), 0).Select
Selection.Copy
Sheets("Profile").Activate
ActiveSheet.Paste
ActiveCell.Select
Application.CutCopyMode = True
i = i + 1
Loop
End Sub

thanks

John

 

[Richard Schollar]

Hi John

Where on the Profile sheet do you want to paste? Are you trying to copy all the data in the B column (from B12 downwards) or only a subset (eg every second cell)?

 

[Peter_SSs]

John

There seems to be a couple of issues with the code. Could you describe briefly what you want it to do? Then I think some useful suggestions could be made.

 

[ifu06416]

Im aiming to copy values from a sheets called "data" to a sheets called "profile".

Starting at the "profile" sheets I want to move to the data sheet and copy the first value from col B (cell B13) and paste it into what ever cell is active on the profile tab

there is a mistake in the original formula, i only want to move down one space at a time, it should have read

Sub macro222()
Sheets("Data").Activate
Set myR = Range("B12")
i = 0
Do Until myR.Offset(i, 0).Value = ""
myR.Offset((i + 1), 0).Select
Selection.Copy
Sheets("Profile").Activate
ActiveSheet.Paste
ActiveCell.Select
Application.CutCopyMode = True
i = i + 1
Loop
End Sub

Regards

 

[Peter_SSs]

I don't think you are attacking this in the best possible way, but I think your initial problem is that you are not re-activating the 'Data' sheet before the loop begins again.

The second issue is, that all the cells are going to get coopied into the same cell on the 'Profile' sheet so all you will see after the code is that last value in whatever cell was active (on 'Profile') when the code started.

Try this for a start but if we can understand better I think we can probably suggest some more efficient code.

Always best to indent your code and post it indented here - much easier to read and de-bug. See my signature block for how.

Sub macro222()
    Sheets("Data").Activate
    Set myR = Range("B12")
    i = 0
    Do Until myR.Offset(i, 0).Value = ""
        myR.Offset((i + 1), 0).Select
        Selection.Copy
        Sheets("Profile").Activate
        ActiveSheet.Paste
        Sheets("Data").Activate
        i = i + 1
    Loop
End Sub

 

[ifu06416]

okay the full code is;

Sub macro222()
Range("A2:w43").Select
Selection.Copy

Dim i As Long
For i = 45 To 90000 Step 43
If Range("A" & i).Value = "" Then Exit For
Next i
Range("A" & i).Select
'Selection.Copy
ActiveSheet.Paste
ActiveCell.Select
Application.CutCopyMode = True

ActiveCell.Offset(0, 0).Select

ActiveCell.Offset(24, 0).EntireRow.Hidden = True
ActiveCell.Offset(26, 0).EntireRow.Hidden = True
ActiveCell.Offset(28, 0).EntireRow.Hidden = True
ActiveCell.Offset(30, 0).EntireRow.Hidden = True
ActiveCell.Offset(32, 0).EntireRow.Hidden = True
ActiveCell.Offset(34, 0).EntireRow.Hidden = True

Sheets("Data").Activate
Set myR = Range("B7")
i = 0
Do Until myR.Offset(i, 0).Value = ""
myR.Offset((i + 1), 0).Select
Selection.Copy
Sheets("Profile").Activate
ActiveSheet.Paste
Sheets("Data").Activate
i = i + 1
Loop
End Sub

I am copying a series of discriptions {Range("A2:w43")} in the "profiling" tab that are assigned via a VLOOKUP function and pasting them 43 cell down.

The lookup value should be placed in cell A2. To save me manually entering this value 100's of time im trying to create acode that will copy the lookup values which begin in cell B7 on the data tab and copy them into cell A2 on the profiling tab.

I beleive that as the cell A2 is active when the range A2:W43 is copied that it is not neccessary to account for the paste location moving down the spreadsheet (the 43 step accounts for this, i think [at least it has so far]).

 

[Peter_SSs]

The lookup value should be placed in cell A2. To save me manually entering this value 100's of time im trying to create acode that will copy the lookup values which begin in cell B7 on the data tab and copy them into cell A2 on the profiling tab.

I beleive that as the cell A2 is active when the range A2:W43 is copied that it is not neccessary to account for the paste location moving down the spreadsheet (the 43 step accounts for this, i think [at least it has so far]).

A2 (Profile) is certainly active when you copy. However after you copy your code selects cell A45 and pastes. So now A45 is selected and that is where all the values from Data B8 down get pasted, including a blank cell at the end. At least that is how it is panning out for me.

I'm not sure if you are aware that you can click in your code then step through a line at a time with the F8 key and keep swapping back and forth to your sheets to see what the last action did. That can be helpful when de-bugging. (Just be careful to leave the same sheet and cell selected before you go back to your code for the next F8)