Excel - Macro to copy and paste

[pbcharlie]

Hi there I am trying to create a macro that searches a colum and until in finds a cell populated with a number and copies that to the cells below until the cell matches a specific values.
To be honest the codeing is beyond me, tips and pointers in the right direction would be a start. The whole thing would get you a beer. Thanks in advance
Charlie

 

[pbcharlie]

so far I have written this
Sub Macro2()
'
' Macro1 Macro
' Macro recorded 21/02/2003 by Charlie Snedden
'
' Keyboard Shortcut: Ctrl+q
'

Application.ScreenUpdating = True






ActiveCell.Offset(1, 0).Select




Do While IsEmpty(ActiveCell)



ActiveCell.Offset(1, 0).Select

Loop




Application.ScreenUpdating = True
End Sub Thanks in advance
Charlie

 

[pbcharlie]

I now have
Sub Macro2()
'
' Macro1 Macro
' Macro recorded 21/02/2003 by Charlie Snedden
'
' Keyboard Shortcut: Ctrl+q
'

Application.ScreenUpdating = True

Do
ActiveCell.Select
Selection.Copy

ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
ActiveCell.Select



If (ActiveCell = "--------------------&quot Then
ActiveCell.Offset(1, 0).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Else

ActiveCell.Offset(1, 0).Select
End If

Loop

Application.ScreenUpdating = True
End Sub

But I am still having problems
Thanks in advance
Charlie

 

[andrew299]

A problem that jumps out at me stright away is that you are pasting a value to a cell

ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
ActiveCell.Select

and then asking what the cell value is. This will simply replace all cells with the first cell value.
you need to check the cell value before you paste - if I have understood your first post correctly. Which you are then doing with your if statement

A useful thing to do is use the debug step into function (F8) which will run one line of code at a time so you can see exactly what is happening. I reduce the excel window and VB window to half the screen size so I can watch both at the same time.

Andrew299

 

[andrew299]

Try this adaptation of you code
What it does is take the value in the activecell then copies it and moves down on. Checks the valure in the cell. If it is not the value (6 in this case) it carries on pasting this value in al;l the cells. When it does find (6) it copies this instead and crries on untill a blank cell is found. Is this the sorrect interprtation of what you wanted?
Sub Macro2()


Application.ScreenUpdating = True

Do Until ActiveCell.Value = ""
ActiveCell.Select
Selection.Copy

ActiveCell.Offset(1, 0).Select




If ActiveCell = "6" Then
'ActiveCell.Offset(1, 0).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
' ActiveCell.Offset(1, 0).Select
Else
'ActiveCell.Offset(1, 0).Select

ActiveSheet.Paste

'ActiveCell.Offset(1, 0).Select
End If

Loop

Application.ScreenUpdating = True
End Sub

Andrew299