Free Microsoft Excel 2013 Quick Reference

Print only visible rows and a print to or save as pdf Macro

ok having a problem i am working on a template for a proposal for work. I had to put in enough room to be able to hold 20 boxes that contain information depending how big the project is that were doing. I have a macro for appling a filter which makes it so only the used boxes are shown(plus other info that doesnt get filtered out).
the formula i used to determine if the row needed to be filtered is thisall filter data is in column A)

=IF($M$226=0,"hide row","print")

The macro i used is:
 Selection.AutoFilter
    ActiveSheet.Range("$A$16:$A$1084").AutoFilter Field:=1, Criteria1:="=print" _
        , Operator:=xlOr, Criteria2:="="
Now heres where im stumped i made a macro assigned it to a button for printing.when i print it out now, instead of the visible data it prints out about 10 or so blank pages where the info that was filtered out would be if it where shown in the printout. I used a simple macro to print which is probably part of the problem

Sub Print1()
ActiveSheet.PrintOut
End Sub
Id like to have it only print the parts of the worksheet that visible. And possibly a macro for a print to PDF or save as pdf that will allow me to choose where i save and name the file. If possible id like the macro to be able to be assigned to 2 separate buttons. thank you in advance for any and all ur help

ok having a problem i am working on a template for a proposal for work. I had to put in enough room to be able to hold 20 boxes that contain information depending how big the project is that were doing. I have a macro for appling a filter which makes it so only the used boxes are shown(plus other info that doesnt get filtered out).
the formula i used to determine if the row needed to be filtered is thisall filter data is in column A)

=IF($M$226=0,"hide row","print")

The macro i used is:
Selection.AutoFilter
ActiveSheet.Range("$A$16:$A$1084").AutoFilter Field:=1, Criteria1:="=print" _
, Operator:=xlOr, Criteria2:="="
Now heres where im stumped i made a macro assigned it to a button for printing.when i print it out now, instead of the visible data it prints out about 10 or so blank pages where the info that was filtered out would be if it where shown in the printout. I used a simple macro to print which is probably part of the problem

Sub Print1()
ActiveSheet.PrintOut
End Sub
Id like to have it only print the parts of the worksheet that visible. And possibly a macro for a print to PDF or save as pdf that will allow me to choose where i save and name the file. If possible id like the macro to be able to be assigned to 2 separate buttons. thank you in advance for any and all ur help


How can i print only seleted rows in an excel spreadsheet ie rows 1,
13,25,50,125,200-210, 265 to one page report or on one page

Hi all

here goes I have a "Active X' button that I use to save a sheet to a folder in PDF format and add the sheet information to an index sheet.

What I would like is to be able to add a hyperlink to the saved PDF and the details on the index sheet,

this is what I have so far

HTML Code: 
Private Sub CommandButton5_Click() 'Save As PDF

Dim FileName As String
Dim Path As String
FileName = Worksheets("Quote - Worksheet").Range("C105") & Range("J111") &
Range("C111")
Path = "C:Documents and SettingsGlennMy DocumentsRaymondGlennDraftsTest"

 Application.ScreenUpdating = False
 
 ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    FileName:=Path & FileName & ".pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False
 
   Sheets("Quote - Worksheet").Select  'Add to Index
   Sheets("Quote").Visible = False
   ActiveSheet.Unprotect
    
   With Worksheets("Index").Range("A1")
    RowCount = .Range("A1").CurrentRegion.Rows.Count
    .Offset(RowCount, 0).Value = ActiveSheet.Range("I100")
    .Offset(RowCount, 1).Value = ActiveSheet.Range("D2")
    .Offset(RowCount, 2).Value = ActiveSheet.Range("C105")
    .Offset(RowCount, 3).Value = ActiveSheet.Range("C111")
    End With
    
    
  'ActiveSheet.hyperlinks.Add anchor:=Cells(i, "A"), Address:=strPath & Txt & ".txt",
TextToDisplay:=Txt & ".txt"
    
   
    ActiveSheet.Protect
 End Sub
Thanks for looking Rex.

Hi,

I am stuck trying to find a solution to the following problem. What is clear is that it should not be written in VBA because I need the result to appear in every cell in column AW.

To summarise;

I have two macros that hide or unhide rows. Now I need a way to count only the non-hidden rows. The total needs to be visible in each visible cell of a single column (presently column AW).

Example - if rows 2 and 3 are hidden using the "Hide Row" macro, the following would happen;

Row 1 is visible, so it is numbered "1" in AW1.
Row 2 is not visible, so it either has no number or freezes at "1" in AW2.
Row 3 is not visible, so it also has no number or freezes at "1" in cell AW3.
Row 4 is visible, so it is numbered "2" in AW4.
And so on.

It doesn't really matter what is in the AW cell in hidden rows because I am not totalling rows. What I am doing is using it to define a print area. If a page has to be 70 rows long, another macro uses column AW to work out where to put page breaks (after row 70, 140, 210 and so on). If twenty rows were hidden on page 1, without the above solution, the printed page would only be 50 rows long. A solution to the above problem will always make sure only visible rows are counted when calculating where to put page breaks.

I am using Excel 2000.

I would be very grateful for any help because I'm really stuck on this one.

Thanks,

Dom

I am using Filters (in macros) to get different views of data.

I need to copy only the visible rows, Columns A to C and F.

When I copy the visible rows (the entire row), I get all the rows including the rows that are not visible.
-------------------
I'm eating fish and taking my vitamins but nothing is working!

Help Please?, Thanks, Craigm

Hey guys,

We need to have a formula that will count all visible rows on a sheet
(well in a perticular array actually. What we have is a sheet that
looks something like this

Name Data1 Data2 Data3
john smith 11 21 33
john smith 12 22 31
john smith 23 333
john smith 14 24 33
john smith 15 25 34
john smith 26 35
john smith 17 3
john smith 18 28 33
john smith 19 29 33

And we have auto filtering enabled on the top row. We would like a
formula that can count the number of visable rows after they are
filtered. Either a formula or VBA would work.

Make sence?

Is there a way (custom view, filter, etc) to only print rows in a print area
that have a value greater than x amount in a certain column? For instance, I
have a worksheet that shows all outstanding accounts. Each account has it's
own row (and the sheet has a couple of header rows). One of the columns
shows the total days overdue the account is. However, I only want to print
the header rows and the subsequent rows with account balances greater than 90
days. It seems incredible there is no easy way to do this, but I have been
searching Excel help without any luck.

Thanks

anyone know of a macro to copy only visible cells in a range?

I would like to use countif or something that works like that on a
autofiltered column to count occurences of several values, like a
frequency list.

The problem is that not just the visible cells are counted but the
hidden ones as well.
Is there a way to do this, subtotal can count all visible rows in a
column but I need to separate the different values.

/Johan

--
johli
------------------------------------------------------------------------
johli's Profile: http://www.excelforum.com/member.php...o&userid=27388
View this thread: http://www.excelforum.com/showthread...hreadid=469427

No question here, just a procedure for the archive.
Search critera:

Select only visible cells in a selected range
manually select just the visible cells in a subset of the whole page
Prompt target to select specific specifically range and only visible cells

Sub SelectOnlyVisibleCellsInSelectedRange()

'Selects only the visible cells in a selected range.
On Error Resume Next
Dim MyRange As Range
Set MyRange = Application.InputBox( _
prompt:="Select Range of Cells to Evalute", Type:=8)
MyRange.SpecialCells(xlCellTypeVisible).Select

End Sub

I would like to use countif or something that works like that on a autofiltered column to count occurences of several values, like a frequency list.

The problem is that not just the visible cells are counted but the hidden ones as well.
Is there a way to do this, subtotal can count all visible rows in a column but I need to separate the different values.

/Johan

hi all , i need to be able to select only visible rows and columns that contain data, eg if columns A,B,C,D,E up to row 26 for example, then i want to select this group, copy it and then transfer it all to sheet 2, (even if there are a couple of blanks in the selection i want to include them also) if more rows of data is included then they will be included as well, if this is possible then can it be used in a macro? and if so how do i go about it please?

My Excel VBA reads a large input file (700K text rows) and writes to 15 smaller files. Initially I created .txt files, but now the user wants the files to be formatted (Landscape Mode with New Courier 8 point font). So I changed the VBA to use Open for Output, Print the rows just like .txt files, and save as .doc instead of .txt. I did this because creating documents in Word took considerably longer.

Then after I closed the .doc, I opened it in Word and ran the statements at the bottom. Unfortunately, the formatting did not change, and in fact, the embedded page breaks I had added are lost!

Can I do what I want in VBA, i.e. use Open for Output, use Print statements, save as .doc, open in Word, and reformat? In Windows Explorer I can open the .doc and make the formatting changes so I don't know why it's not working in VBA.

The time to have VB do everything in Word is prohibitive so I want to create the documents as if I were simply writing text, and then after saving, Open in Word and quickly reformat.

Thanks,
Pete

Sub FormatWordDoc(WordApp As Word.Application)
' set font and margins

With WordApp.ActiveDocument.Range
.Font.Name = "Courier New"
.Font.Size = 8
End With

With WordApp.ActiveDocument.PageSetup
.Orientation = wdOrientLandscape
.TopMargin = WordApp.InchesToPoints(0.6)
.BottomMargin = WordApp.InchesToPoints(0.6)
.LeftMargin = WordApp.InchesToPoints(0.6)
.RightMargin = WordApp.InchesToPoints(0.6)
End With
End Sub

I have a project where I'm using VBA to update multiple excel
workbooks and creating custom copies of the workbooks as PDF files.
The custom copies of the workbooks are customized by what sheets and
individual wants to see. I have successfully used PDFwriter to
suppress the "Save as PDF File" dialog by programatically changing the
registry entries for PDFFileName, bExecViewer and bDocInfo and using
the printOut method of excel to get what I need. This works well for
all but two of the workbooks. These two workbooks create the PDF files
as programmed but at the end of the print to PDF before control
returns to the VBA code, the "Save as PDF File" dialog pop's up. I
acts as if either PDFwriter or Excel does not recognize that it had
already output a PDF copy of the workbook.

This is anoying as I have not been able to figure out what is
triggering the dialog box to pop up after the print to PDF is
complete. Has anyone experienced this or have any possible solutions
for this situation? I'm using Excel 2000 and Acrobat 5.0.

Trying to obtain "Save As" Functionality when clicking on a Excel or Word doc on my desktop.

Hi!  I have XP and I have Office Excel and Word 2007.  I have saved several Excel spreadsheets to my desktop in which I wanted to save as another file format without opening them up.  How do I obtain the Save As functionality when I right click on an excel file when it is on my desktop? 
 
Thanks!

How do I create a workesheet that has only 10 rows and 10 columns

Hi there,
In column A I have some rows with the word Total. I want
to select those rows and copy to another worksheet
starting in A2.
I been trying to modify some code but without success.
Can anyone provide some info on this?
Appreciate all the help.

Thanks,

Juan

hi guys wondered if someone more knowledgeable could help
im using this coding
Sub Button1_Click()
Sub SaveJobFile()
 'declare all variables
  Dim stOfPath As String
  Dim ActualMidFolder As String
  Dim EndOfNameAndPath As String
  Dim FileNameToSave As String
  Dim JobNum As String
  Dim PONum As String
  Dim SuppliersName As String

  'define initial variables (note: the ranges may need to be changed
    JobNum = Range("G23").Value
    EndOfNameAndPath = JobNum & " sheet hello " & Format(Date, " dd.mm.yy") &
".pdf"

  'test to see if it is stock or PO (?) & define the saving path accordingly
    Select Case UCase(Left(JobNum, 1)) = "J"
        Case True
  'Rob's test path:     stOfPath = "C:Documents and SettingsHP_OwnerMy Documents"
            stOfPath = Range("B69") & ""
            'test for any likely folders
                If Not (DoesFileFolderExist(stOfPath & JobNum & "*")) Then GoTo TheEnd
            'identify the exact folder
                ActualMidFolder = GetActualFolderName(stOfPath, JobNum) & ""
            'define rest of file name
                EndOfNameAndPath = "Docs" & EndOfNameAndPath
                FileNameToSave = stOfPath & ActualMidFolder & EndOfNameAndPath
        Case False
            stOfPath = Range("B70") & ""
            If Not (DoesFileFolderExist(stOfPath)) Then GoTo TheEnd
                FileNameToSave = stOfPath & EndOfNameAndPath
    End Select

  'save the file & finish macro
    ActiveWorkbook.SaveAs Filename:=FileNameToSave
    
     

    Exit Sub

TheEnd:
  'warn nothing exists & end the macro
    MsgBox "Macro ending b/c no Folder with the following number exists : " & stOfPath & JobNum &
"*", , "FYI"
    Debug.Print stOfPath & JobNum & "*"
End Sub

Public Function DoesFileFolderExist(strfullpath As String) As Boolean
'sourced from www.excelguru.ca/node/30 by Ken Puls
'note it only checks for the existence of the lowest folder (or the file) in the strfullpath string.
If Not Dir(strfullpath, vbDirectory) = vbNullString Then DoesFileFolderExist = True
End Function

Function GetActualFolderName(StartOfPath As String, StartOfFuzzyFolder As String)
'sourced & modified from http://www.themssforum.com/ExcelProgramming/parent-folder/
Dim oFSO As Object
Dim SubDirectory
Set oFSO = CreateObject("Scripting.FileSystemObject")
With oFSO.GetFolder(StartOfPath)
    For Each SubDirectory In .SubFolders
        If UCase(Left(SubDirectory.Name, Len(StartOfFuzzyFolder))) = UCase(StartOfFuzzyFolder) Then
            GetActualFolderName = SubDirectory.Name
            Exit For
        End If
    Next SubDirectory
End With
'free memory
Set oFSO = Nothing

End Function
and it saves as pdf exactly to where i need it to go.

but when i go to open the pdf i get a message saying adobe reader could not open J7404 sheet hello 26.04.12.pdf because it is either not a supported file type or because the file has been damaged ( for example, it was sent as an email attachment (NOT THE CASE) and wasnt correctly coded.

Is there an Excel macro that simulates the effect of specifying the
rows and columns to repeat in a printout? I am looking for something
that simulates the following operations:

1) Page Setup->Sheet [Tab]->Rows to repeat at Top
Highlight the rows

2) Page Setup->Sheet [Tab]->Columns to repeat at Left
Highlight the columns

Thanks,
Gus

Hi,
I have written one macro which will change background color of some of the rows of one excel file, and save it with the changes. This excel file is having some macros which will run automatically every time we open this. That means it is a micro enabled sheet (.xlsm). But while saving it, with below code:
Application.DisplayAlerts = False
Application.ScreenUpdating = False
objExcelSrc.ActiveWorkbook.SaveAs fileName:= _
MrkSrcFile, FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
one “Microsoft Excel” error message is displaying. The error message says:
“The following Features cannot be saved in macro-free workbooks: .VB Project. To save a file with these features, click No, and then choose a macro-enabled file type in the File Type List. To Continue saving as a micro – free workbook, Click Yes”.
If I click “Yes”, my macro is running fine. If NO, it’s asking to change the format and save it manually.
So, I have two options to suppress the message or to click the default option Yes so that my macro will run. But I am not able to handle this with the below two options.
Below are the codes I have already tried, but not able to handle. Please guide me on this.
'---control moving out
'objExcelSrc.ActiveWorkbook.SaveAs fileName:= _
'MrkSrcFile, FileFormat:= _
'xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
'---control moving out
'objExcelSrc.ActiveWorkbook.SaveAs fileName:=
'MrkSrcFile , FileFormat:= _
'xlOpenXMLWorkbookMacroDisabled, CreateBackup:=False
'---control moving out
'objExcelSrc.ActiveWorkbook.SaveAs fileName:= _
'MrkSrcFile, CreateBackup:=False
'Application.DisplayAlerts = True
'currentWorkSheetSrc.SaveAs MrkSrcFile
'Application.DisplayAlerts = False
'currentWorkSheetSrc.SaveAs fileName:= _
'MrkSrcFile, FileFormat:= _
'xlOpenXMLWorkbook, CreateBackup:=False
'--51 pop ups,56-hang,50-not working
'objExcelSrc.ActiveWorkbook.SaveAs fileName:= _
'MrkSrcFile, FileFormat:=FileFormatNum, CreateBackup:=False
currentWorkSheetSrc.Application.Quit
'MsgBox (j)
Application.ScreenUpdating = True
Thanks
Amarendra

Fail to "save" or "save as" in Word 2007

I hope that someone out there can help me! I'm running Office 2007 with Windows XP. Everything was working just fine until the most recent Windows update just prior to this weekend.
Over the weekend I began noticing a problem. In Word, when creating a new document or saving an existing document with a new name, I am unable to save or "save as". When I attempt to save or save as, I hear an error sound, but nothing appears on the screen - no dialogue box, nothing.
The problem doesn't occur consistently. It happens more often than not, but not every time. If I reboot and then relaunch Word, I sometimes am able to save or save as.  I've tried launching Word in safe mode, but the problem occurs occasionally even in Safe Mode.
I've got all the latest updates for Office 2007. I've run diagnostics, anti-virus and spyware programs, you name it, and nothing fixes it. I have been unable to find a fix for my problem, and I've desperately hunted everywhere on the web.
Might anyone out there be able to help me to find a fix for this annoying and persistent issue? Thanks kindly in advance!

hi,
i wrote this code to copy my company logo along with the function to copy some checked rows.I tried to do this in one macro
here is the code


	VB:
	
 
 
Sub AddSheetandCopy() 
    Dim sShape As Shape 
    Dim wsNew As Worksheet 
    Dim wsStart As Worksheet 
    Sheet1.Shapes("Picture 1").Copy 
    Sheet2.Range("A1").PasteSpecial 
     
    Set wsStart = ActiveSheet 
    Set wsNew = Sheets.Add() 
    For Each sShape In wsStart.Shapes 
        With sShape 
            If .FormControlType = xlCheckBox Then 
                If .ControlFormat.Value = xlOn Then 
                    wsStart.Range(.TopLeftCell.Address).EntireRow.Copy _ 
                    Destination:=wsNew.Cells(Rows.Count, 1).End(xlUp)(2, 1) 
                     
                End If 
            End If 
        End With 
    Next sShape 
    wsNew.DrawingObjects.Delete 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have drop down boxes which have actions which run when the box changes.

However i do not want these to run when i do a save or a save as. For some reason when i save it refreshed all the boxes and runs the assocaited code.

How do i code to prevent this running when i do a save or save as?

Cheers

Nic

Excel 2002 XP PC

Good Morning -

I have been working to display text of my 7 Column spreadsheet on a webpage. I have the sheet formatted as I want it to appear. I click "View or Save as Webpage" and All columns transfer to the Webpage view as I want Except One. Its a Column with a URL address in it.

It seems the problem is with Word Wrap. Other Cells with words and spaces display in blocks on the webpage view. The URL because of its continuous string of text does not. It Breaks the text at the end of the designated width and only displays the top line.

Example - If you Start a new Open Worksheet --

Insert this Text in Cell A1 --

Pyrex Glass Basket used in the Microchip manufacturing industry for washing wafers - each one is serial Numbered

Insert this URL Into A2

http://i44.photobucket.com/albums/f1...reStuff016.jpg

Select View as Webpage

With word Wrap formatted to ON, and column width selected, Cell A2 looks like this in th4e Worksheet. ( and thats how I want it to appear on the webpage ) ( Note that A1 with Word Wrap does display properly because there are spaces in it )

http://i44.photobucket.c
om/albums/f1...reStuff01
6.jpg

With Word Wrap on when viewed in WEBPAGE VIEW it only displays the text from the top line of the worksheet cell.

http://i44.photobucket.c

Without Word wrap it displays complete but way to long to use in my 7 column display.

If I insert spaces in the long text string in the worksheet;

http://i44.photobuck et.com/albums/f1...re Stuff016.jpg

with Word Wrap it displays in the worksheet as

http://i44.photobuck
et.com/albums/f1...re
Stuff016.jpg

and it displays in the WEBPAGE VIEW as

http://i44.photobuck
et.com/albums/f1...re
Stuff016.jpg

that is the format I need it to appear in but, the URL address is not valid because of the spaces.

How can I make this long URL string appear in word wrapped form in Excel's "View as Webpage" option ?

OR

How else can I lead viewers to that URL to View the item photos without using half my page width on just that one column ?

Thanks for Looking -

While trying to save or save as, we are able to get to my computer and to the
C drive. But when we attempt to open a folder in the C Drive excel locks up
and we are forced to reboot.
Has anyone else had this problem or know of a solution? any suggestions are
greatly appreciated.