Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column index
This is just part of a module code I have. The problem I am having is when
the paste data it only paste to one colunm (B). I need it to paste from col "B" to "AG" Any ideas?? Sub NewPeriod() If MsgBox("Confirm you are ready to create the next 28 day period?" & Chr(13) & Chr(13) & "Note: The data for the 28 day period twelve months ago" & Chr(13) & "will be permanently lost.", vbYesNo + vbQuestion, "Duty Sheet") = vbYes Then Else Exit Sub P = Range("CC18") Range("B7:AG51").Copy Cells(643 - (45 * P), 2).PasteSpecial Paste:=xlPasteValues Range("CC18") = 0 Range("B103:AG687").Copy Range("B58").PasteSpecial Paste:=xlPasteValues Range("B643:AG687").ClearContents Range("CC16").Formula = Range("CC16") + 45 Range("B7:AG51").ClearContents Range("B7").Select End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column index
I don't see anything in your code that looks like it would only paste into a
single column. Are you sure that your data being copied has more than column worth of information in it? I'd step through the code (using F8) and toggle back to excel to see what was going on. Geo wrote: This is just part of a module code I have. The problem I am having is when the paste data it only paste to one colunm (B). I need it to paste from col "B" to "AG" Any ideas?? Sub NewPeriod() If MsgBox("Confirm you are ready to create the next 28 day period?" & Chr(13) & Chr(13) & "Note: The data for the 28 day period twelve months ago" & Chr(13) & "will be permanently lost.", vbYesNo + vbQuestion, "Duty Sheet") = vbYes Then Else Exit Sub P = Range("CC18") Range("B7:AG51").Copy Cells(643 - (45 * P), 2).PasteSpecial Paste:=xlPasteValues Range("CC18") = 0 Range("B103:AG687").Copy Range("B58").PasteSpecial Paste:=xlPasteValues Range("B643:AG687").ClearContents Range("CC16").Formula = Range("CC16") + 45 Range("B7:AG51").ClearContents Range("B7").Select End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column index
I sort it out by writing long hand!! is there a shorter way of doing this?
Sub NewPeriod() If MsgBox("Confirm you are ready to create the next 28 day period?" & Chr(13) & Chr(13) & "Note: The data for the 28 day period twelve months ago" & Chr(13) & "will be permanently lost.", vbYesNo + vbQuestion, "Duty Sheet") = vbYes Then Else Exit Sub P = Range("CC18") Range("B7:B51").Copy Cells(643 - (45 * P), 2).PasteSpecial Paste:=xlPasteValues Range("C7:C51").Copy Cells(643 - (45 * P), 3).PasteSpecial Paste:=xlPasteValues Range("D7:D51").Copy Cells(643 - (45 * P), 4).PasteSpecial Paste:=xlPasteValues Range("E7:E51").Copy Cells(643 - (45 * P), 5).PasteSpecial Paste:=xlPasteValues Range("F7:F51").Copy Cells(643 - (45 * P), 6).PasteSpecial Paste:=xlPasteValues Range("G7:G51").Copy Cells(643 - (45 * P), 7).PasteSpecial Paste:=xlPasteValues Range("H7:H51").Copy Cells(643 - (45 * P), 8).PasteSpecial Paste:=xlPasteValues Range("I7:I51").Copy Cells(643 - (45 * P), 9).PasteSpecial Paste:=xlPasteValues Range("J7:J51").Copy Cells(643 - (45 * P), 10).PasteSpecial Paste:=xlPasteValues Range("K7:K51").Copy Cells(643 - (45 * P), 11).PasteSpecial Paste:=xlPasteValues Range("L7:L51").Copy Cells(643 - (45 * P), 12).PasteSpecial Paste:=xlPasteValues Range("M7:M51").Copy Cells(643 - (45 * P), 13).PasteSpecial Paste:=xlPasteValues Range("N7:N51").Copy Cells(643 - (45 * P), 14).PasteSpecial Paste:=xlPasteValues Range("O7:O51").Copy Cells(643 - (45 * P), 15).PasteSpecial Paste:=xlPasteValues Range("P7:P51").Copy Cells(643 - (45 * P), 16).PasteSpecial Paste:=xlPasteValues Range("Q7:Q51").Copy Cells(643 - (45 * P), 17).PasteSpecial Paste:=xlPasteValues Range("R7:R51").Copy Cells(643 - (45 * P), 18).PasteSpecial Paste:=xlPasteValues Range("S7:S51").Copy Cells(643 - (45 * P), 19).PasteSpecial Paste:=xlPasteValues Range("T7:T51").Copy Cells(643 - (45 * P), 20).PasteSpecial Paste:=xlPasteValues Range("U7:U51").Copy Cells(643 - (45 * P), 21).PasteSpecial Paste:=xlPasteValues Range("V7:V51").Copy Cells(643 - (45 * P), 22).PasteSpecial Paste:=xlPasteValues Range("W7:W51").Copy Cells(643 - (45 * P), 23).PasteSpecial Paste:=xlPasteValues Range("X7:X51").Copy Cells(643 - (45 * P), 24).PasteSpecial Paste:=xlPasteValues Range("Y7:Y51").Copy Cells(643 - (45 * P), 25).PasteSpecial Paste:=xlPasteValues Range("Z7:Z51").Copy Cells(643 - (45 * P), 26).PasteSpecial Paste:=xlPasteValues Range("AA7:AA51").Copy Cells(643 - (45 * P), 27).PasteSpecial Paste:=xlPasteValues Range("AB7:AB51").Copy Cells(643 - (45 * P), 28).PasteSpecial Paste:=xlPasteValues Range("AC7:AC51").Copy Cells(643 - (45 * P), 29).PasteSpecial Paste:=xlPasteValues Range("AD7:AD51").Copy Cells(643 - (45 * P), 30).PasteSpecial Paste:=xlPasteValues Range("AE7:AE51").Copy Cells(643 - (45 * P), 31).PasteSpecial Paste:=xlPasteValues Range("AF7:AF51").Copy Cells(643 - (45 * P), 32).PasteSpecial Paste:=xlPasteValues Range("AG7:AG51").Copy Cells(643 - (45 * P), 33).PasteSpecial Paste:=xlPasteValues Range("CC18") = 0 Range("B103:AG687").Copy Range("B58").PasteSpecial Paste:=xlPasteValues Range("B643:AG687").ClearContents Range("CC16").Formula = Range("CC16") + 45 Range("B7:AG51").ClearContents Range("B7").Select End Sub "Dave Peterson" wrote: I don't see anything in your code that looks like it would only paste into a single column. Are you sure that your data being copied has more than column worth of information in it? I'd step through the code (using F8) and toggle back to excel to see what was going on. Geo wrote: This is just part of a module code I have. The problem I am having is when the paste data it only paste to one colunm (B). I need it to paste from col "B" to "AG" Any ideas?? Sub NewPeriod() If MsgBox("Confirm you are ready to create the next 28 day period?" & Chr(13) & Chr(13) & "Note: The data for the 28 day period twelve months ago" & Chr(13) & "will be permanently lost.", vbYesNo + vbQuestion, "Duty Sheet") = vbYes Then Else Exit Sub P = Range("CC18") Range("B7:AG51").Copy Cells(643 - (45 * P), 2).PasteSpecial Paste:=xlPasteValues Range("CC18") = 0 Range("B103:AG687").Copy Range("B58").PasteSpecial Paste:=xlPasteValues Range("B643:AG687").ClearContents Range("CC16").Formula = Range("CC16") + 45 Range("B7:AG51").ClearContents Range("B7").Select End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column letter to index | Excel Discussion (Misc queries) | |||
Column index number | Excel Discussion (Misc queries) | |||
Naming of column index | Excel Worksheet Functions | |||
Using an Index by Column Headings instead of Column Numbers | New Users to Excel | |||
Column Index | Excel Worksheet Functions |