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]

Could you please help I dnt know exactly what to do

 

[RichardMGreen]

When the code stops and gives you the error message, click debug so the line of code is highlighted in yellow.
Then point the mouse at counta on that line and it will give you it's current value.

 

[tv9_rohith]

counta = 0

 

[RichardMGreen]

There's your problem. You're trying to write to row zero which doesn't exist.
Edit the code to this:-

Dim count As Long
Dim count2 As Long
Dim counta As Long
counta=1
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
counta = counta + 1
Loop
End Sub

 

[tv9_rohith]

In the sheet 2 I am getting even Header of sheet 1

and the data in the sheet2 is coming as :

<TABLE style="WIDTH: 158pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=210><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7680" width=210><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 158pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=210>Name</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>"Blank Cell"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Rohith</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>"Blank Cell"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>"Blank Cell"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>kumar</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>"Blank Cell"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>"Blank Cell"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>"Blank Cell"</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>Jenni</TD></TR></TBODY></TABLE>


5th Value in Sheet1 is not at all coming in sheet 2

 

[RichardMGreen]

Try this instead:-

Dim count As Long
Dim count2 As Long
Dim counta As Long
counta=1
count = Sheets("Sheet1").Range("A2").CurrentRegion.Rows.count
count2 = Sheets("Sheet2").Range("A2").CurrentRegion.Rows.count
Do Until counta = count
count2 = count2 + 1
Sheets("Sheet2").Range("A" & count2).Value = Sheets("Sheet1").Range("A" & counta).Value
counta = counta + 1
Loop
End Sub

 

[tv9_rohith]

I am not getting the blank cells now but only the thing is I am getting even header in sheet 2.

and I have entered 5 values in Column A sheet 1 and I didnt get the 5th value in sheet 2 instead I got the Header value from sheet 1

 

[RichardMGreen]

Just to check, do you want every row copying over but only certain columns?
And do you want whatever is being copied over to overwrite what is already there or to be added to the bottom of the list?

 

[tv9_rohith]

as you said "do you want every row copying over but only certain columns?" - Yes

and there will no overwriting things.

==

once the data is in sheet 2 I will take that into seperate spreadsheet for processing and then will clear everything for the next use.

so there will be no chance of overwriting the document.

 

[RichardMGreen]

In that case, you can use formulas instead of VBA.
In sheet2 cell A1 you want the following:-
=Sheet1!A2
Copy this across and edit the column reference for each column you want to copy.
Then copy it down as far as necessary until you have all the rows you need.