How to copy selected column to another page under a specifik field?

[robissme]

Hi.
How do you make a macro that copies the selected column to anothe
sheet/page in the same file, the contents of the selected column will g
in specifically for a column where a WEEKNUM be indicated. Thus, th
column is selected, the user presses for x a button where macro is run
the user will be asked, for example, through a dialog box (To whic
WEEKNUM want to copy the column to?). User types such as 23, and clic
OK. 23 is actually located in as: Y:2 in another page. The column to b
copied must therefore fall directly under Y:3

(The dialogbox isnt important, the user could type the weeknu
elsewhere).
Someone who can help me

+-------------------------------------------------------------------
|Filename: helpme.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=399
+-------------------------------------------------------------------

 

[Claus Busch]

Hi,

Am Mon, 4 Jun 2012 18:02:58 +0000 schrieb robissme:

How do you make a macro that copies the selected column to another
sheet/page in the same file, the contents of the selected column will go
in specifically for a column where a WEEKNUM be indicated. Thus, the
column is selected, the user presses for x a button where macro is run,
the user will be asked, for example, through a dialog box (To which
WEEKNUM want to copy the column to?). User types such as 23, and click
OK. 23 is actually located in as: Y:2 in another page. The column to be
copied must therefore fall directly under Y:3

(The dialogbox isnt important, the user could type the weeknum
elsewhere).
Someone who can help me?

try:

Sub myCopy()
Dim LRow As Long
Dim Answer As Integer

LRow = Sheets("Selected").Cells(Rows.Count, 1).End(xlUp).Row
Answer = Application.InputBox("Which weeknum is the destination?", _
Type:=1) 'enter only a number

If Answer = 0 Then
MsgBox "Please enter a weeknum"
Exit Sub
End If

Sheets("Selected").Range("A1:A" & LRow).Copy _
Destination:=Sheets("Copy-to").Cells(4, Answer)
End Sub


Regards
Claus Busch

 

[Bob Flanagan]

The following subroutine will do the task. It also checks to see if
the week is between 1 and 52

Sub CopyColumn()
Dim C As Integer
Do
C = Application.InputBox(prompt:="Enter the week number",
Type:=1)
If C = False Then Exit Sub
If C >= 1 And C <= 52 Then Exit Do
Loop

ActiveCell.EntireColumn.Copy Sheets("sheet2").Columns(C)

End Sub

Robert Flanagan
Add-ins.com LLC
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, fax 302-234-9859
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel