Need help in writing formula for the query????

[tv9_rohith]

In the Spreadsheet I have 2 Sheets.

1sheett for the Complete information about the Employee - Name, Location, Sex, Job Role, Salary.

2sheet only the required fields are - Name, Location, Job Role.

My query is :

If I enter data in the sheet 1 and I click the Command Button in Sheet 1 ( i.e., Generate Report Button ) then automatically the required column information should display in Sheet 2.

For example I have enterd 6 rows of data in sheet1 then all the 6 rows information should display in Sheet 2.

 

[tv9_rohith]

Can anyone help me on this ... pls..

 

[bensonsearch]

Hi,
So you just want the data entered on sheet1 to be copied to sheet2? at the bottom of exsisting data?

 

[tv9_rohith]

what ever I enter in the sheet 1 it should display in the sheet 2 but I dnt require all the columns but only few.

for example

in sheet 1 there are columns like this - Name, Sex, location , Age, Salary.

in sheet 2 I need only Name,location and salary.


when I enter the data in sheet1 and click on the Commmand button it should through the information to sheet 2

 

[bensonsearch]

hi,

if it is a formula for cells that get replaced

on sheet2 a1 enter

=sheet1!A1

this will make sheet2 a1 = sheet1 a1 (change as needed for coloums)

if you are after VBA

dim count as long
dim count2 as long
dim counta as long
 
 
counta = 1 
 
count = sheets("Sheet1").Range("A1").CurrentRegion.Rows.count
count2 = sheets("Sheet2").Range("A1").CurrentRegion.Rows.count 
 
do until counta = count
count2=count2 + counta
sheets("Sheet2").range("A" & count2).value =  sheets("Sheet1").range("A" & counta).value
 
'repeat the above for the coloums you want making sure to match them, ie if name is on sheet 1 in A then it is A on sheet2 (or whereever you have it)
counta = counta + 1
loop

 

[tv9_rohith]

when I use the below mentioned formula then in the sheet2 I getting data but include the header value and hender value and the 2 value is coming with 1 cell blank in the middle ( same row ) and the 2 value and the 3 rd value are having 2 blanks in the middle and its increasing the blank cell between the cells to 3, 4, 5, 6

dim count as long
dim count2 as long
dim counta as long


counta = 1

count = sheets("Sheet1").Range("A1").CurrentRegion.Rows.count
count2 = sheets("Sheet2").Range("A1").CurrentRegion.Rows.count

do until counta = count
count2=count2 + counta
sheets("Sheet2").range("A" & count2).value = sheets("Sheet1").range("A" & counta).value

'repeat the above for the coloums you want making sure to match them, ie if name is on sheet 1 in A then it is A on sheet2 (or whereever you have it)
counta = counta + 1
loop

 

[bensonsearch]

change the below

count = sheets("Sheet1").Range("A2").CurrentRegion.Rows.count
count2 = sheets("Sheet2").Range("A2").CurrentRegion.Rows.count

and also

counta = 2

give that a try and let me know

 

[tv9_rohith]

As per your advice I have tried with the below mentioned Code but only the thing that error has rectified is Header is not coming in the sheet 2 but the gaps between cells is increasing and the last value in sheet 1 is not at all coming in sheet2

dim count as long
dim count2 as long
dim counta as long

counta = 2
count = sheets("Sheet1").Range("A2").CurrentRegion.Rows.count
count2 = sheets("Sheet2").Range("A2").CurrentRegion.Rows.count
do until counta = count
count2=count2 + counta
sheets("Sheet2").range("A" & count2).value = sheets("Sheet1").range("A" & counta).value
'repeat the above for the coloums you want making sure to match them, ie if name is on sheet 1 in A then it is A on sheet2 (or whereever you have it)
counta = counta + 1
loop

Could you please check it.

 

[bensonsearch]

ok change

count2=count2 + counta


to

count2 = counta

 

[tv9_rohith]

when I try with the below mentioned code I getting error :

Run Time error '1004':

Application - defined or object -defined error

and the formula I have pasted is :
Private Sub CommandButton1_Click()
Dim count As Long
Dim count2 As Long
Dim counta As Long
count2 = counta
count = Sheets("Sheet2").Range("D2").CurrentRegion.Rows.count
count2 = Sheets("Sheet3").Range("A2").CurrentRegion.Rows.count
Do Until counta = count
count2 = count2 + counta
Sheets("Sheet3").Range("A" & count2).Value = Sheets("Sheet2").Range("D" & counta).Value
counta = counta + 1
Loop
End Sub