selecting a range from a user defined reference in another sheet

[rhk001]

Hi there, thanks for looking I am trying to select a series of columns based on a user defined reference, then copy those ranges and paste then in a new sheet. This is sort of what I have come up with, and it does not seem to be quite working.

1. Is this a reasonable way of getting my answer?
2. Can anyone spot where I am going wrong?


Sub Choose_Column() ' This routine will use 3 worksheets, "Front", "data" and "new"
'In Sheet Front I will ask the user which coulumns in Sheet "Data"
' has the followng fields "Proj Id", "Act Id", "Activity Name"

Dim PiC As String
Dim IdC As Range
Dim NaC As Range
Dim R As Integer
'Sheets("front").Select

' Get inputdata
R = 4 ' Starting with Row 4
With Sheets("front") ' Using the sheet "front" look at the values in Column C and selected rows
PiC = .Cells(R, 3) ' PiC should now equal the value of Row4, Col3 in "Front"
IdC = .Cells(R + 1, 3) ' Idc should now equal the value of Row5, Col3 in "Front"
NaC = .Cells(R + 2, 3) ' Nac should now equal the value of Row4, Col3 in "Front"

End With
' Select Ranges from "Data", copy , then pastes into "New"
' The next thing I want to do is by using the values in Pic, Idc,Nac is look in Sheet Data and use them as the reference to select the ranges

Sheets("data").Select ' Go to sheet data (which has loads of fields)
Range("Pic,Idc,Nac").Select ' select the 3 columns
ActiveSheet.[A65536].End(xlUp)(1).Select
Selection.Copy ' copy
Sheets("new").Select 'now move into sheet "new"
ActiveSheet.[A65536].End(xlUp)(2).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

End Sub

[p45cal]

Try this and then if it's beginning to work (I've made shed loads of assumptions) - come back:

Sub Choose_Column() ' This routine will use 3 worksheets, "Front", "data" and "new"
'In Sheet Front I will ask the user which coulumns in Sheet "Data"
' has the followng fields "Proj Id", "Act Id", "Activity Name"
Dim PiC As String
Dim IdC As String
Dim NaC As String
Dim R As Integer
'Get inputdata
R = 4 'Starting with Row 4
With Sheets("front") ' Using the sheet "front" look at the values in Column C and selected rows
PiC = .Cells(R, 3) ' PiC should now equal the value of Row4, Col3 in "Front"
IdC = .Cells(R + 1, 3) ' Idc should now equal the value of Row5, Col3 in "Front"
NaC = .Cells(R + 2, 3) ' Nac should now equal the value of Row4, Col3 in "Front"
End With
With Sheets("Data")
Intersect(.UsedRange, Union(.Columns(.Rows(1).Find(PiC).Column), .Columns(.Rows(1).Find(IdC).Column), .Columns(.Rows(1).Find(NaC).Column))).Copy Destination:=Sheets("new").[A65536].End(xlUp)(2)
End With
End Sub

[rhk001]

Hey P45cal thanks for the help much appreciated the info you sent helps to get the first coulumn in , I am still pretty new at all this so forgive me for the stupid questions.

I have 3 sheets,
1. Front - Used selects the columns he wants
2. Data - Has lots of columns of data
3. New - where the code copies the columns selected by the user (Front) from (data) and pastes into new.

I have attached the spreadsheet, my inital suspcions are that my "With" statement is not working.

[p45cal]

I don't have xl 2007, could you post a 2003 compatible version?

[rhk001]

Hi P45cal,

here is a 97/2003 version of the spreadsheet.

[p45cal]

Try the attached, it uses the actual column headers rather than asking for the column they're in.

[rhk001]

Hi P45cal, that works really well thanks very much for your help.

[lucas]

You could resize the columns by adding this at the end of the second with statement for easier reading:

Sheets("new").Columns.AutoFit