I neglected to mention that the sheet with the data is named "Master".
Change that in the code to the actual sheet name
My apologies for that
Posts by jolivanes
-
-
Display MoreCode
- Sub Maybe()
- Dim sh As Worksheet
- Application.ScreenUpdating = False
- For Each sh In ThisWorkbook.Worksheets
- If sh.Name <> "Master" Then
- If WorksheetFunction.CountIf(Columns(15), sh.Name) <> 0 Then
- With Sheets("Master").Cells(1).CurrentRegion
- .AutoFilter 15, sh.Name
- .Offset(1).SpecialCells(12).Copy sh.Cells(Rows.Count, 1).End(xlUp).Offset(1)
- End With
- End If
- End If
- Next sh
- Sheets("Master").AutoFilterMode = False
- Application.ScreenUpdating = True
- End Sub
- This goes through all sheets except the Master. I might have misunderstood that you only want to transfer 2 sheet names.
- If so, let us know
-
-
We all (well, most of us anyway) started the same way you did and guess what, we all learned as we went along.
The way to go in my opinion is to attach a workbook with a before and after, different sheets if that suits you, and an explanation in it that explains how you arrived at the answers.
My personal preference is to have everything in one workbook. I usually have a "data" sheet with all info and have the result in another sheet. Mind you, having it in the same sheet off to the side is possible also of course. Other people have different ideas but there are many roads leading to Rome and they all get there.
The gentleman from Post #3 has a world of experience so stick close to what he suggest
-
"The second filter is in another sheet and it's simply trying to filter out all values that aren't zero."
Which sheet is this in your attachment?
"Worksheets("Instructions").Range("A6").Value"
Where is this sheet in your attachment?
Attach a representative workbook.
-
Re: "The first filter is based on a value in a dropdown list"
Where is that dropdown in your attachment?
-
BTW, if your drop down list values have a leading 0 (zero) they are not numerical values but text (String).
So "CostCenter" would not be an Integer but a String.
-
Attach a workbook so people don't need to recreate it to test.
-
Re: "The code runs without error but won't display the workbook."
Instead of the "Sub Button12_Click()" code, what if you use
-
You also forgot to mention that you have cross posted which means you have asked the same question in other forum(s).
Most will shut you down until you post the links to the other site where you cross posted.
Do so in all the sites.
-
Re: "Sorry I am posting all my questions here...."
Why? I am sure everyone here is glad to see you here. Don't be shy. Come back anytime.
The code from dangelor has you covered and is faster also.
In your code, if you change this
to this
and
to this
You would have had a home run.
You could also have the three lines from If to End If in a single line like so
But stick to dangelor's code as that is the way to go.
-
With the information provided!
Change all references where required.
Data depending, there are faster ways but this might be the easiest to understand.
Display MoreCode- Sub Maybe()
- Dim lc As Long, i As Long
- Dim sh1 As Worksheet, sh2 As Worksheet
- Set sh1 = Worksheets("Sheet1")
- Set sh2 = Worksheets("Sheet2")
- lc = sh1.Cells.Find("*", , , , xlByColumns, xlPrevious).Column
- Application.ScreenUpdating = False
- With sh1
- For i = lc To 1 Step -1
- If .Cells(1, i).Value = "RECEIVED" Then
- .Columns(i).Copy sh2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
- .Cells(1, i).EntireColumn.Delete Shift:=xlToLeft
- End If
- Next i
- End With
- Application.ScreenUpdating = True
- End Sub
-
If the amount of data is not too big, looping should be OK.
Display MoreCode- Sub Maybe()
- Dim sh1 As Worksheet, sh2 As Worksheet
- Dim i As Long
- Dim c As Range
- Set sh1 = Worksheets("database")
- Set sh2 = Worksheets("ORDERS")
- For i = 2 To sh2.Cells(Rows.Count, 1).End(xlUp).Row
- For Each c In sh1.Range("A2:A" & sh1.Cells(Rows.Count, 1).End(xlUp).Row)
- If c.Value = sh2.Cells(i, 1).Value Then c.Offset(, 3).Value = c.Offset(, 3).Value * sh2.Cells(i, 2).Value
- Next c
- Next i
- End Sub
-
You wrote a whole book but no specific requirement on what and where.
There is no way you'll need that many macros. Looping through Ranges and Sheets is a normal occurrence.
Just let us know what needs to happen, where it needs to happen and what the condition is.
The best is to attach a workbook with a before and after with an explanation on how the "after" was arrived at.
There should be sufficient data in the workbook to understand the needs.
-
"but the last two rows" I assume you mean Columns here.
Attach your workbook. Without seeing the code it is just guesswork on how everything is set.
-
With numerical values, something like this should work.
This is just an example as you should notice.
Display MoreCode- Sub Example()
- Dim c As Range
- For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
- Select Case c.Value
- Case 0 To 19
- c.Offset(, 2) = "Junior"
- Case 20 To 29
- c.Offset(, 2) = "Senior"
- Case 30 To 39
- c.Offset(, 2) = "Old Man"
- Case 40 To 49
- c.Offset(, 2) = "Very Old Man"
- Case Else
- c.Offset(, 2) = "Ancient"
- End Select
- Next c
- End Sub
-
Just an observation.
If a person is 70 years old, which Case would you take?
The first 4 are all true.
-
Set the width of the columns to a better "to fit" size.
-
-
We should not be advertising other sites but I hope it is allowed in this case.