Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Column index
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Geo Geo is offline
external usenet poster
  
Posts: 66
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
Geo Geo is offline
external usenet poster
  
Posts: 66
Default 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 With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Column letter to index Radon Excel Discussion (Misc queries) 3 February 2nd 09 05:29 AM
Column index number helpwanted Excel Discussion (Misc queries) 2 January 19th 09 05:36 PM
Naming of column index Freshman Excel Worksheet Functions 2 October 30th 07 07:57 AM
Using an Index by Column Headings instead of Column Numbers Sean Bishop New Users to Excel 2 November 21st 06 09:38 PM
Column Index Megan Excel Worksheet Functions 2 November 11th 04 02:09 PM


All times are GMT +1. The time now is 07:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement