I basically need to clean up this code that I made up myself to save me from repeatedly copy and pasting! Please advise?
Sub cp()
Worksheets("copy").Select
Range("b2").Select
Selection.copy
Range("b7").Select
ActiveSheet.Paste
Range("c2").Select
Selection.copy
Range("e7").Select
ActiveSheet.Paste
Range("d2").Select
Selection.copy
Range("h7").Select
ActiveSheet.Paste
Range("e2").Select
Selection.copy
Range("k7").Select
ActiveSheet.Paste
end sub
Sub cp
Dim ws as Worksheet
Set ws = Worksheets("copy")
ws.Range("B7") = ws.Range("B2")
ws.Range("E7") = ws.Range("C2")
ws.Range("H7") = ws.Range("D2")
ws.Range("K7") = ws.Range("E2")
End Sub
??
It's best to avoid selecting, copying and pasting when possible. Try and refer directly to the cells, ranges, worksheets and workbooks you want. So an improvement here might be to specify the workbook
Dim wb as workbook
Set wb = workbooks("MyWorkbook")
dim ws as worksheet
Set ws = wb.Sheets("copy")
...
That way excel will never get confused about which cells you are refering to. It's especially handy when opening a workbook, or creating a new workbook.
Dim wb as workbook
Set wb = workbooks.add 'Creates a new workbook, and binds it to the variable wb, so you always know any action on wb is being done to that new workbook
Hope that helps some
edit - in response to your comment--
Instead of using Range, you can use Cells, which takes a row and column number instead of a column letter-
Dim x as Integer
Dim ws as Worksheet
Set ws = Worksheets("copy")
For x = 2 to 500
ws.Cells(7,x) = ws.Cells(2,x)
Next x
Try setting a breakpoint on the For loop, and then step through it with F8. I dont completely understand the pattern you're going for (Moving B down 5 rows, shifting the value in C to E, etc.), but if you can tweak that to suit your needs it sounds like you'll have enough to complete your project
