Replies

Well, yes I know that ….. I kind of want to go backwards in the process. I want to copy the cells and then paste the sum.

It may not make sense why I would want to do this, and I’d love to show my spreadsheet, but I can’t because it is sales information.

Thank you so much for telling me it worked for you. That helped me solve my issue. The problem was that some of the items in column A were in Uppercase & some in lowercase. (I didn’t realize the code would evaluate it literaly) Once I changed all the data to uppercase I reran the code and had no problem.

Thank you so much Hans, for looking at this for me!

Thank you so much for telling me it worked for you. That helped me solve my issue. The problem was that some of the items in column A were in Uppercase & some in lowercase. (I didn’t realize the code would evaluate it literaly) Once I changed all the data to uppercase I reran the code and had no problem.

Thank you so much Hans, for looking at this for me!

That’s interesting. I wasn’t aware of that. That was the default that was in the options (I didn’t actually change it myself)

I appreciate the info!

-Leslie

Thank you! Now I know about a new function!

I sure do appreciate it..

The things you learn everyday

I added the On Error Resume Next before the For Line……

Sub TBCO_FormatHCDetailSheets()

Application.StatusBar = “Creating Head Coach Detail”

Sheets(“Smith Pipe”).Select

Dim ws As Worksheet
On Error Resume Next
For Each ws In Worksheets(Array(“Smith Pipe”, “Smith Fcst”, “Cole Pipe”, “Cole Fcst”, “Flock Pipe”, “Flock Fcst”))
ws.Activate
With ActiveSheet

End With
Next ws

End Sub

What is happening now is that I have the following worksheets on this batch – Smith Fcst, Smith Pipe, Flock Fcst, Flock Pipe. What happens is that it performs all the code on the Smith Fcst worksheet, but then completely ends the subroutine and moves on to the next subroutine. So it’s not really moving on to the next worksheet.

What do you think is happening?

(I don’t have to worry about the user changing the name of the worksheets, as the worksheets are created automatically right before this sub is performed with no available user intervention – Well I’m sure there probably is a way for user intervention, but believe me – my 2 coworkers who are using this will just yell for me if there is an issue

I am posting an example. Usually the Report Sheet has 4 different sections using a variety of either column L or M on the FDD Consolidator tab.

One thing to note, when I tried this with CountA it gave me back 385 (the number of rows I went down)

I cannot use a Pivot Table to accomplish what i need to accomplish. You would have been able to see that If I hadn’t deleted the rest of the report.

Well, I’m going to have to practice on this one!

I have:

=COUNT(IF((‘FDD Consolidator’!$W$2:$W$448=$A$3&$A5&S$3)*(‘FDD Consolidator’!$C$2:$C$448=$A$1),’FDD Consolidator’!$C$2:$C$448)) (With Ctrl-Shift-Enter)

And I should have a result of 2 for that cell, but it is returning 0. My only other option is to add columns to my FDD Consolidator sheet, which is what I was trying to avoid.

Thanks for the help and the links. I’m going to have to work on this one to find out what I am doing wrong!

Found it!
Didn’t search hard enough on the web before I posted

=MID(A2,FIND(” “,A2,1)+1,LEN(A2))

Legare,

That’s really cool, I like how the sheets are created based upon the name in column A. I was able to find a solution using the Advanced Filter (Except for one of my Macros’s where it is causing Excel to crash… Doesn’t make sense to me. 2 of my module’s work fine with the code, but my third module crashes with it)

Thank you…

I just had to follow up to say Wow! I just ran my full report, and it used to take 4 minutes to run.

Now it only takes 15 seconds!

You guys are the best! I wouldn’t have been able to even start on this project without all the help you have given me here and in the Excel forum…

Andrew! I could kiss you

I put the Criteria in the proper worksheets during my first part of my code (Run via a Menu Command – I run one part, check for data accuracy and then run the rest)

In the second portion I put in this sub

Sub FiltertoSheets()

Sheets(“Adams Pipe”).Select
Dim ws As Worksheet
For Each ws In Worksheets(Array(“Adams Pipe”, “Adams Fcst”, “Jones Pipe”, “Jones Fcst”, “Doe Pipe”, “Doe Fcst”))
ws.Activate
With ActiveSheet

Sheets(“FDD Consolidator”).Columns(“A:AA”).AdvancedFilter Action:= _
xlFilterCopy, CriteriaRange:=Range(“A1:B2”), CopyToRange:=Range(“A4”), _
Unique:=False

End With
Next ws
End Sub

Thank you! Thank you! Thank you! Thank you!

I think that may just work.. I’ll look into it and let you all know If I succed!

Thanks for the reply, but I have to do this in VBA.

This portion of my code is a small portion of a larger piece. It works right now, but it’s sloooow. I just figured I was doing it the wrong way, ’cause I’m new at this.

Hi Legare,

Yes the code can sort the worksheet before the copy. There is a header row at the top of the sheet (row 1).

Thanks!