rhk001
05-04-2009, 04:26 AM
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
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