Thank you Dinesh Kumar Takyar 😃 If I want it to print without showing the dialogue! Just press button it will be print by a printer immediately and another button for another printer?
Sir How can we loop through a folder and copy non contiguous cells or ranges from different sheets in multiple workbooks to my master sheet? Your help and support is highly appreciated
These links will guide: www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/ www.exceltrainingvideos.com/get-data-from-multiple-sheets-in-multiple-workbooks-into-master-workbook-with-vba/ www.exceltrainingvideos.com/tag/copy-paste-non-adjacent-cells-excel/ Or search www.exceltrainingvideos.com
This Video is very useful. But if I have to Select Multiple Sheet i.e. sheet1, Sheet2 and Sheet3 by holding Control button, then how to know the total number of pages in a msg box or in a cell ? let me expalin : I have 4 sheet namely --- "Balance Sheet", "Trading Account", "Profit & Loss Account", "P&L Appropriation A/C" and a another sheet named "Invioce" . "Balance Sheet" may be varies to 3 or 4 pages, Profit & Loss Account may be 2 or 3 pages. I take Rs. 30 for each page being Composed by me along with the 1st Printout I make a sheet named "Invoice" Page in which -- "b13" cell is "Total No of Pages" , "D13" is "Rate per page " and "e13" is "Total amount" Now I have to Count the Printable page of All the 4 sheets and should be written in the cell "b13" in the sheet named as "Invoice" to calculate the total amount of bill.
Try this code: Dim totpages As Long, gTotPages As Long MsgBox Worksheets.Count totpages = 0 gTotPages = 0 For i = 1 To Worksheets.Count With Worksheets(i) totpages = Worksheets(i).PageSetup.Pages.Count gTotPages = gTotPages + totpages End With Next i MsgBox gTotPages Please note that adding data to sheet is easy.
Hello Sir, How to create user Login form? i need user enter the user name and password then open excel sheet with press login button. how i do this? pls explain to me thank you
found the code to process the current sheet of the open excel file (except for the page with no data). Sub Test () Dim a, i As Long, totPages As Long a = GetPageArea (ActiveSheet) If IsArray (a) Then For i = 1 To UBound (a) If Application.CountA (a (i)) Then totPages = totPages + 1 Next i End If MsgBox "The number of print pages on the worksheet is" & totPages End Sub Function GetPageArea (sht As Worksheet) Dim a, i As Long, v, z As New Collection On Error Resume Next z.Add Key: = "R1", Item: = 1 For Each v In sht.HPageBreaks z.Add Key: = "R" & v.Location.Row, Item: = v.Location.Row Next v i = sht.Cells.SpecialCells (xlCellTypeLastCell) .Row + 1 z.Add Key: = "R" & i, Item: = i On Error GoTo 0 If z.Count Then ReDim a (1 To z.Count - 1) For i = 1 To UBound (a) Set a (i) = Intersect (sht.UsedRange, sht.Rows (z (i) & ":" & (z (i + 1) - 1))) Next i GetPageArea = a End If End Function
Try this code with your PATH and study this Link: www.exceltrainingvideos.com/tag/import-data-from-emails-into-excel-worksheet-with-vba/ For Each OutlookMail In Folder.Items If OutlookMail.Subject Like "Our order no 2 for 3000 silk scarves assorted" Then sHTML_Body = OutlookMail.HTMLBody Debug.Print sHTML_Body Exit For End If Next OutlookMail
Thanks for this wonderful video
I agree : ) Dinesh makes awesome videos : )
ExcelIsFun sure does
Thank you
I would like to know how specify a printer to be printed if I have already two or three setup printers?
Try this code:
Application.Dialogs(xlDialogPrinterSetup).Show
Thank you Dinesh Kumar Takyar 😃
If I want it to print without showing the dialogue!
Just press button it will be print by a printer immediately and another button for another printer?
How to do this same thing of counting pages with word doc. Please help 🙏🏻
Sir
How can we loop through a folder and copy non contiguous cells or ranges from different sheets in multiple workbooks to my master sheet?
Your help and support is highly appreciated
These links will guide: www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/
www.exceltrainingvideos.com/get-data-from-multiple-sheets-in-multiple-workbooks-into-master-workbook-with-vba/
www.exceltrainingvideos.com/tag/copy-paste-non-adjacent-cells-excel/
Or search www.exceltrainingvideos.com
This Video is very useful. But if I have to Select Multiple Sheet i.e. sheet1, Sheet2 and Sheet3 by holding Control button, then how to know the total number of pages in a msg box or in a cell ?
let me expalin :
I have 4 sheet namely --- "Balance Sheet", "Trading Account", "Profit & Loss Account", "P&L Appropriation A/C" and a another sheet named "Invioce" .
"Balance Sheet" may be varies to 3 or 4 pages, Profit & Loss Account may be 2 or 3 pages.
I take Rs. 30 for each page being Composed by me along with the 1st Printout
I make a sheet named "Invoice" Page in which -- "b13" cell is "Total No of Pages" , "D13" is "Rate per page " and "e13" is "Total amount"
Now I have to Count the Printable page of All the 4 sheets and should be written in the cell "b13" in the sheet named as "Invoice" to calculate the total amount of bill.
Try this code:
Dim totpages As Long, gTotPages As Long
MsgBox Worksheets.Count
totpages = 0
gTotPages = 0
For i = 1 To Worksheets.Count
With Worksheets(i)
totpages = Worksheets(i).PageSetup.Pages.Count
gTotPages = gTotPages + totpages
End With
Next i
MsgBox gTotPages
Please note that adding data to sheet is easy.
Thanks
Hello Sir, How to create user Login form? i need user enter the user name and password then open excel sheet with press login button. how i do this? pls explain to me thank you
agher mai active x listbox mai saari sheet add karo phir mai un me se ek sheet kar ke oos ki total pages kitne he tou ap es pe ke tutorial banae sir
ua-cam.com/video/ghX6jPhKlf0/v-deo.html
सर अगर एक्सेल के हर पेज की अलग अलग pdf बनानी हो और उनका नेम भी उसे शीट से देना हो तो कैसे हो पाएगा। प्लीज हेल्प...
This Excel VBA lesson will guide: www.exceltrainingvideos.com/convert-multiple-sheets-to-multiple-pdf-files/
counts the number of pages (except for pages with no data). how ?
If I've understood your query, the macro does it based on certain page parameters like a4 size, margins, data, etc.
can you make a video of my problem ?
found the code to process the current sheet of the open excel file (except for the page with no data).
Sub Test ()
Dim a, i As Long, totPages As Long
a = GetPageArea (ActiveSheet)
If IsArray (a) Then
For i = 1 To UBound (a)
If Application.CountA (a (i)) Then totPages = totPages + 1
Next i
End If
MsgBox "The number of print pages on the worksheet is" & totPages
End Sub
Function GetPageArea (sht As Worksheet)
Dim a, i As Long, v, z As New Collection
On Error Resume Next
z.Add Key: = "R1", Item: = 1
For Each v In sht.HPageBreaks
z.Add Key: = "R" & v.Location.Row, Item: = v.Location.Row
Next v
i = sht.Cells.SpecialCells (xlCellTypeLastCell) .Row + 1
z.Add Key: = "R" & i, Item: = i
On Error GoTo 0
If z.Count Then
ReDim a (1 To z.Count - 1)
For i = 1 To UBound (a)
Set a (i) = Intersect (sht.UsedRange, sht.Rows (z (i) & ":" & (z (i + 1) - 1)))
Next i
GetPageArea = a
End If
End Function
Sir recently i joined UA-cam. Plz can you to code for. A macro to extract source of each mail. To excel or in outlook itself. Where it can be. Plz..
This link will help:
www.exceltrainingvideos.com/get-data-from-outlook-into-excel-worksheet/
Dinesh Kumar Takyar sir, i want to view html. Source of email
Try this code with your PATH and study this Link:
www.exceltrainingvideos.com/tag/import-data-from-emails-into-excel-worksheet-with-vba/
For Each OutlookMail In Folder.Items
If OutlookMail.Subject Like "Our order no 2 for 3000 silk scarves assorted" Then
sHTML_Body = OutlookMail.HTMLBody
Debug.Print sHTML_Body
Exit For
End If
Next OutlookMail
Not work
www.exceltrainingvideos.com/count-number-of-print-pages-on-worksheet-with-vba/