need macro to not copy hidden cells

[abhisharma]

Hi Team,

I am attaching the excel , in this excel i have macro but this macro is copying hidden cells as well in sheet2 so i want to copy only visible cells after dropdown in sheet one.

 

[p45cal]

Application.ScreenUpdating = False
With Sheets("Sheet1")
Dim head(3), datas(3)
Dim x
y = 26
Do
If .Rows(y).Height > 0 Then
For yy = 0 To 3
head(yy) = .Cells(y + yy, "B")
datas(yy) = .Cells(y + yy, "C")
Next yy
With Sheets("Sheet2")
yy = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
If yy < 2 Then yy = 2
If .Cells(1, "A") = Empty Then
For x = 1 To 4
.Cells(yy, x) = head(x - 1)
Next x
yy = yy + 0
End If
For x = 1 To 4
.Cells(yy, x) = datas(x - 1)
Next x
End With
End If
y = y + 4
Loop Until .Cells(y, "B") = Empty
End With
Application.ScreenUpdating = True


Add the code in red. It looks only at therow height of the 1st row of each group of 4 to determine whether to copy that group of 4.

(ps. Sorry, I can't make the code indent properly and highlight the lines to be added)

 

[abhisharma]

thank you so much for the help. It is working fine.

 

[abhisharma]

Hi p45cal

I need small help on this attach excel.

When i am selecting cell 17 drop down "simple" it should return any 2 list on below layout and if you select any other drop down it should not make any changes.

Please find attached excel.

 

[p45cal]

I don't understand.

 

[abhisharma]

apology...!!

In attached excel at Sheet1 i have one drop down , from drop down if i select the value simple , it should display two records of simple user.
and Drop Down:-

name

Simple

like :-

Name	abhishek
id	345​
email	a@mail.com
role	Super User
Name	rohan
id	890​
email	rman@mail.com
role	P User

 

[p45cal]

What makes a person a 'simple' user?
I can only see Super User and P User in your example above.
You have 3 sheets, where do you want this short list to display?

 

[abhisharma]

means from drop down i will select simple from drop down
You have 3 sheets, where do you want this short list to display?------------> on sheet1 itself

name

Simple

and sort list show only two record below on sheet1 it self

Name	abhishek
id	345
email	a@mail.com
role	Super User
Name	rohan
id	890
email	rman@mail.com
role	P User

 

[p45cal]

How do I know that abhishek is a 'simple ' user?
How do I know that rohan is a 'simple ' user?

 

[abhisharma]

both are no simple user.

Both user's role same as below list.

Name	abhishek
id	345
email	a@mail.com
role	Super User
Name	rohan
id	890
email	rman@mail.com
role	P User

i need macro like :-

If we select from drop down value = "Simple"

on the sheet 1 it should display layout of two user's details as above.

Currently it is showing all simple user when we selecting drop drown value = "Simple" . i want here is like it should display only any two users details.

 

[p45cal]

I'm completely at a loss.
I think you should start a new thread and explain very, very clearly what you want. If you attach a workbook, only include in it what's relevant and explain what is relevant. For example, where do you want data to appear, where does that data come from, how to decide which data is manipulated, does that data disappear with a new selection from the dropdown or is it added to? And lot's more.

I'm unwatching this thread.

 

[abhisharma]

thanks for the help. I will create a new thread.