need help

[rexxar]

i totally new to this VB. and also macro in excel.
i need to do basically searching copy and paste.
for example
enter name to active cell then the macro will search for the name and copy all the data.on other sheet then patse to the active sheet
please any body can help me

[RobDog888]

Welcome to the Forums.

To start learning VBA easiest way is to record a macro doing the tasks you need. Then stop recording and check out the generated macro code in the VBA IDE (Alt+F11).

Your going to want to look at the .Find method.

[rexxar]

tanks for the head start..
but rite now i`m stuck..
here is my plan..
i`ve stated 1 variable let say name
so i want macro to search the name in other sheet then if found macro will copy all the data beside the name than transfer to previous sheet..
the thing is i donnot know how to move the activecell .. can assign active cell as variable

[Webtest]

rexxar:

Is the name on the "other" sheet(s) always in a particular column? Are you moving data from the reference sheet from particular columns to the same columns or to different columns on the active sheet? Are you copying ALL of the data on the row from the reference sheet to the active sheet? What happens if you don't find the selected name on the reference sheet? Please be a little more specific with the format of the data. What you are trying to do is not difficult.

[RobDog888]

Dont use Name as a variable as its a reserved word.

Post your code.

[rexxar]

sory man i`ve been away for quite some time..

this is my code

Sub DecideUserInput()
Dim bText As String
Dim bsheet As String
Dim rng As Range
Dim rng2 As Range
Dim i As Integer
Dim y As Integer
Dim r As Long
Dim c, d As Integer
Dim c1 As Integer
Dim c2 As Integer
Dim a As Integer
Dim b As Integer
 
 ' here is the INPUTBOX-function :
    bText = InputBox("Insert in a text", "This accepts any input")
    ' here is the INPUTBOX-method :
    ActiveCell.FormulaR1C1 = bText
    Set rng = Sheet1.UsedRange
    'Set rng2 = Sheet1.UsedRange.Rows
   'a = Selection.Column.Value        ' i tried to extract the location of the active cell so that i can get row and column number
   'b = Selection.row.Value
    
    Worksheets("sheet2").Select
     Range("b1").Select
   ' initial state
   c = 4 'colloum
   i = 1 'row
   d = a + 1
   c1 = c + 1
   c2 = c
   Do
   For i = 1 To 60
   If Sheet2.Cells(i, c).Value = bText Then
           Do
             For y = c1 To 8
                Sheet2.Cells(i, y).Copy
                Sheet1.Select
                Sheet1.Cells(b, d).Paste
              Next y
           Loop Until y = 8
            
            Sheet2.Select
            Sheet2.Cells(i, c2).Copy
            Sheet1.Select
            Sheet1.Cells(1, 3).Select
            activeselection.Paste
   End If
   Next i
   Loop Until i = 60
   
   
   End Sub

it`s not pretty because i try and error.. one thing, when i tried to run this code my excel spreadsheet crash..

[Webtest]

If you really want to learn what goes on in VBA, make the very top line of your module "Option Explicit". Setting this option requires that you, the programmer, must properly define and declare each and every variable. If you don't set this option, VBA will make some assumptions and automatically assign data types to variables if you don't. This can lead to some unusual problems, such as if you misspell a variable name ... VBA will just create and use a new variable with the incorrect spelling!

Also, remember that you can get a lot of helpful code templates by using the Macro Recorder wizard. Just record snippets of operations and look at the code that is produced for 'hints'.

In the above code, I think there is a problem with variables "Sheet1" and "Sheet2". Here is how I set "Handles" for sheets and copy data:

Dim srcSheet As Worksheet  'Handle for Source Worksheet
Dim dstSheet As Worksheet  'Handle for Destination Worksheet

'Set the handles for the Source and Destination Sheets
Set srcSheet = Sheets("Sheet2")
Set dstSheet = Sheets("Sheet1")
'Copy a cell from the Source Sheet to the Destination Sheet
srcSheet.Cells(1, c2).Copy
dstSheet.Paste (dstSheet.Cells(1, 3))

'At the end of your code, clean up all of the "Set" objects
'This is not absolutely required, but is a good habit to get into
Set srcSheet = Nothing
Set dstSheet = Nothing

[rexxar]

tanx man,,,..
just one question.. how to extract row and column from range..
for example range("a2") i want to get the index number. and represent them in variable so that i can manipulate

[Webtest]

"Row" returns the first Row Index in a range
"Rows.Count" returns the number of Rows in a range

"Column" returns the first Column Index in a range
"Columns.Count" returns the number of columns in a range

aRow = Range("C5:F17").Row
aCol = Range("C5:F17").Column
MsgBox aRow & Chr(10) & aCol