Posts by markc

Re: Sort By Active Cell Column

Ok
Thanks
-marc

Re: Sort By Active Cell Column

Thanks
Always do that
Appears there now, becuase i responded again
But dropped out after your post
Not sure why, unless it is because someone renamed the thread.

Thanks
-marc

Re: Set Variable to Row x & Active Cell Column

Quote

Yes and I never worked out what you wanted there either. You get a lot of help here, but often drop threads and move on with more questions, before having past thread solved. No feedback, no thanks, no nothing!

Apologies Dave.
For some reason the thread dropped from my control panel so I did not relize that I had not responded to Roy. Searched, found the thread, replied that Roy's solution did work and thanked him.

Set rng = Cells(7, ActiveCell.Column)

That does return

Quote

Sort method of Range class failed

When I select G1 for example.
If I select G7, it works fine

I then added

rng.Select

Immediately after and it now works exactly as needed
Thanks for the help
-marc

Final code

Private Sub comp_myMonthlyReport_SortAscend()
    Dim rng As Range


    With ActiveWindow
        Set rng = Cells(7, ActiveCell.Column)
        rng.Select
    End With
    
    Selection.Sort Key1:=rng, _
        Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


End Sub

Re: Sort By Active Cell Column

Apologies all
For some reason this dropped out of my control panel?

By adding the blank row at row 5 as Roy suggested, solved the problem.

Thanks Roy!
-marc

Re: Set Specific Row Of Activecell.column

Thanks Roy -

That returns error:

Quote

Run-time error '1004':
Method 'Range' of object '_Global' failed

Thanks
-marc

Revised code

Private Sub comp_myMonthlyReport_SortAscend()
    Dim rng As Range


    With ActiveWindow
        Set rng = Range(ActiveCell, Range(7, .ActiveCell.Column))
    End With
    
    Selection.Sort Key1:=rng, _
        Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


End Sub

Re: Set Specific Row Of Activecell.column

Thanks Gerikes

Corrected that
New error is

Quote

Run-time error '1004':
Sort method of Range class failed

I intentionally selected row 1 which is above the header row of the data to be sorted (Header at row 6)

I'm trying to program around potential landmines created by end-user if they do not select a row below 6

Thanks
-marc

Private Sub comp_myMonthlyReport_SortAscend()
    Dim rng As Range


    With ActiveWindow
        Set rng = .ActiveCell(7, .ActiveCell.Column)
    End With
    
    Selection.Sort Key1:=rng, _
        Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


End Sub

Hi all -

I have a sort procedure I have been working on. Thread
Now I would like to make sure the row of the activecell.column is row 7

I tried

Private Sub comp_myMonthlyReport_SortAscend()
    Dim rng As Range


    With ActiveWindow
        rng = .ActiveCell(7, .ActiveCell.Column)
    End With
    
    Selection.Sort Key1:=rng, _
        Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


End Sub

But I receive this error:

Quote

Run-time error '91':
Object variable or With block variable not set

What am I doing wrong

Re: Sort Without Header Rows

Thanks -

I don't see any problem, but I could be wrong
Would you mind taking a look see if you see anything?

I copied the page out to a new workbook
Copied the macros into the new workbook
Updated the button to assign the new macro

Just click on a cell below row 5 within the data range and click on the sort button.

For example, the end-user may wish to sort best to worst on the % column
Although that raises a question: How could I modify the macro to let user choose best to worst or worst to best?

I guess easiest would be create 2 buttons.
But that may not be the mose elegant approach.

Thanks
-marc

Re: Sort Without Header Rows

?
The unique labels in row 5 should serve as the header row for the data
The othe rows are in the print layout of the report for "Header Rows"

Thanks
-marc

Re: Sort Without Header Rows

Cell G6 is highlighted at the moment
Header rows at row 5 are unique labels
Row 4: report totals
Rows 3 & 2 :Report Headers

Thanks
-marc

Hi all -

I am trying to perform a sort based on the ActiveCell.Column
I thought my code would exclude the hearer rows, but presently it moves the header rows beneath the data

I tried
Header:=xlGuess as well as
Header:=xlNo

Same result

What am I doing wrong?
Thanks
-marc

Private Sub comp_mySort()
    
    Selection.Sort Key1:=Cells(1, ActiveCell.Column), _
        Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


End Sub

Re: Print: Print Usedrange Except Header Rows

Sorry guys -

One last question
How is UsedRange interpreted?
My template file where all data is pasted into from master file has items in top 4 rows presently.
Row 1 contains some buttons for executing some macros
Are these interpreted as in the UsedRange?
Or should I add a char to every row, and change the color to white if I want it to be incl in the Header of the reports?

Thanks
-marc

Re: Print: Print Usedrange Except Header Rows

Thanks Andy -

That took care of it

-marc

Re: Print: Print Usedrange Except Header Rows

Thanks guys

When I remove the .select I receive an error

Before:

rng.Offset(1, 0).Resize(rng.Rows.Count - 2, rng.Columns.Count).Select

After:

rng.Offset(1, 0).Resize(rng.Rows.Count - 2, rng.Columns.Count)

Error:

Quote

Compile error:
Expected: =

Thanks
-marc

Re: Print: Print Usedrange Except Header Rows

Thanks Roy/Andy -

I am receiving error

Quote

Run-time error '1004':
Unable to set the PrintArea of the PageSetup class

Here

ActiveSheet.PageSetup.PrintArea = rng

Unclear as to why
I merely replace a range like $A$1:$G$12 with the [nr]*[/nr] "rng"

Thanks
-marc

Full code

Sub PrintSht()
    Dim rng As Range
    
    Set rng = ActiveSheet.UsedRange
    rng.Offset(1, 0).Resize(rng.Rows.Count - 2, _
        rng.Columns.Count).Select
    ActiveSheet.PageSetup.PrintArea = rng
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$2:$2"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = rng
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    Application.ActivePrinter = "PDF995 on Ne01:"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "PDF995 on Ne01:", Collate:=True
End Sub

Hi all -

i am trying to come up with some code to print many sheets in a workbook based on user clicking a button on each sheet.

I would like the code to be reusable and based on the activesheet, this way I don't need to write code specific for each sheet.

My first thought was to use something like ActiveSheet.UsedRange, but the rows may span several pages, so I'll need the header row to repeat on each page

Here's a snippet I recorded

ActiveSheet.PageSetup.PrintArea = "$A$2:$E$12"
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$2:$2"

But I can think of a way to use UsedRange but drop out about rows 1-4 which do not need to be printed on the reports

Number of Cols varys on every sheet as well and diff start/stop cols
Ex one sheet A:G, another sheet C:T

Anyone seen anything like this or have some ideas so I don't have to code each page seperately?

Thanks
-marc

Re: Advance Filter Duplicate Headings

Dave -

I am using "cut" to mean filter copy paste to another workbook
Which is why I placed in quotes

In the end I did change the headers
Just curious why Excel ignores the second instance and duplicates the first

Thanks
-marc

Re: Copy From Several Worksheets

Thanks Stanley & Dave
-marc

Hi all -

I just came across an error in one of my workbooks where I had duplicate headers on a few columns

So When I used [advf]*[/advf] to "cut" the data into several workbooks for end users, the data in the first column was pasted 2x and the second columns was ignored altogether.

Headers since changed and that fixed the problem.
I'm just curious why the second column was ignored altogether

Thanks
-marc

Hi all -

I am trying to copy data out of certain sheets to another workbook
Some worksheets in the source workbook should be skipped

Everything works, except the sheets I would like to skip are not skipped
Does anyone see anything in the code that would cause the sheets not to be skipped?

Thanks
-marc

Sub comp_GetData()
    'FSO bit from Bob Phillips via Google Groups
    
    Dim wbBook As Workbook
    Dim wbOpen As Workbook
    Dim wsData As Worksheet
    Dim sht As Worksheet
    Dim FSO As Object
    Dim i As Long
    Dim sFolder As String
    Dim fldr As Object
    Dim Folder As Object
    Dim file As Object
    Dim Files As Object
    Dim blnMatched As Boolean
    Dim lngIndex As Long
    
    
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With
    
    '/File_Folder
        Set FSO = CreateObject("Scripting.FileSystemObject")
        sFolder = "C:\Data\Apple\Budget 2007\Client"
    
    '/Sheets
        Set wbBook = ThisWorkbook
        Set wsData = wbBook.Worksheets("Data")


        
    If sFolder <> "" Then
        Set Folder = FSO.GetFolder(sFolder)
        Set Files = Folder.Files
        cntFiles = Folder.Files.Count
        
        
        '/Process
            For Each file In Files
                Set wbOpen = Workbooks.Open(Filename:=file.Path)
                With ActiveWorkbook
                    For Each sht In ActiveWorkbook.Worksheets
                        Debug.Print sht.Name
                        If sht.Name <> "Detail Analysis" Or _
                            sht.Name <> "Assumptions" Then
                            sht.Range("A14:x168").Copy
                            wsData.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValuesAndNumberFormats)
                        End If
                    Next sht
                End With
                wbOpen.Close
            Next file
    End If
        
    '/Cleanup
        Set wbBook = Nothing
        Set wbOpen = Nothing
        Set wsData = Nothing
        Set sht = Nothing
        Set FSO = Nothing
        Set fldr = Nothing
        Set Folder = Nothing
        Set file = Nothing
        Set Files = Nothing
    
        With Application
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .DisplayAlerts = False
        End With


End Sub