Export Invoice-VBA

Vishal Srivastava · May 20, 2015

Hi Friends,
I need your help regarding this issue. I am working on a project where I have to export the "Tax Invoice" to a new workbook. I am using following code for exporting the workbook:

Code:

Sub Export_Invoice()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
  Dim iname As Variant
  Dim iname2 As Variant
  Dim d As Variant
  Dim shipid As Integer
  Dim shiptext As String
  Dim iWords As String
 
  iname = ActiveWorkbook.Name
  Workbooks.Add
  iname2 = ActiveWorkbook.Name
  Windows(iname).Activate

  Sheets("Invoice").Select
  Cells.Select

  Selection.Copy
  Windows(iname2).Activate
  Columns("A:A").Select
  ActiveSheet.Paste
 
  Cells.Select
  ActiveSheet.Paste
  Selection.Copy
  Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:= _
  False, Transpose:=False
 
  ActiveSheet.Columns("L:P").EntireColumn.Hidden = True
 
  For Each d In ActiveSheet.Shapes
  If Left(d.Name, 4) = "Drop" Then
  d.Delete
  End If
  Next
 
  ActiveWorkbook.Colors(39) = RGB(234, 234, 234)
  Range("A1").Select
  ActiveWindow.DisplayGridlines = False
  Application.CutCopyMode = False
 

 
With ActiveSheet.PageSetup

  .Zoom = False
  .FitToPagesWide = 1

  .FitToPagesTall = False
 
.LeftHeader = ""

.CenterFooter = "Page &8&P of &N"

.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.34)
.BottomMargin = Application.InchesToPoints(0.55)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.3)
.CenterHorizontally = False
.Orientation = xlPortrait

End With

  MsgBox "This invoice has been exported to a new workbook: " & iname2
  Windows(iname).Activate
  Range("A1").Select
 
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Everything works fine except the formatting of the exported book.

In exported workbook, Row height got fit automatically to default excel row size. Fonts got changed from my selected font "Verdana" to default excel font "Calibiri".

I have tried at my best but got no solution to this problem. I just want to export the workbook with the same formats and row height.

Hope You have understood my problem now.

I am also attaching a sample workbook for your reference.

Please help me out.

Thanks & Regards,
CMA Vishal Srivastava

Deepak · May 20, 2015

A lot of thing would be modified in this code but for the now change this & check.

Code:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False

Vishal Srivastava · May 20, 2015

Deepak said:
A lot of thing would be modified in this code but for the now change this & check.

Code:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False

Hi Deepak,
My concern is not only to paste the values also want to have existing formulas to workwith.

Your code will paste formula as value.

Deepak · May 20, 2015

Which xl version you have?

Vishal Srivastava · May 20, 2015

Deepak said:
Which xl version you have?

Professional 2013

Deepak · May 20, 2015

Vishal Srivastava said:
Professional 2013

After exporting it what will you do.

Vishal Srivastava · May 20, 2015

Deepak said:
After exporting it what will you do.

Actually It is a Tax Invoice and client will modify the Invoice to Retail Invoice and will do some modifications according to its need. Any modifications will be done in Exported workbook only.

Deepak · May 20, 2015

This will make a copy a sheet to new workbook without any changes.

Sheets("Invoice").Copy

now do the further.

projected code

Code:

Sub Export_Invoice()
Application.ScreenUpdating = False
Application.DisplayAlerts = False



   
    Dim iname As Variant
    Dim iname2 As Variant
    Dim d As Variant
    Dim shipid As Integer
    Dim shiptext As String
    Dim iWords As String

Sheets("Invoice").Copy
           
    ActiveSheet.Columns("L:P").EntireColumn.Hidden = True
   
    For Each d In ActiveSheet.Shapes
        If Left(d.Name, 4) = "Drop" Then
            d.Delete
        End If
    Next
 
   
    ActiveWorkbook.Colors(39) = RGB(234, 234, 234)
    Range("A1").Select
    ActiveWindow.DisplayGridlines = False
    Application.CutCopyMode = False
   

   
With ActiveSheet.PageSetup


  .Zoom = False
  .FitToPagesWide = 1

        .FitToPagesTall = False
 
.LeftHeader = ""

.CenterFooter = "Page &8&P of &N"

.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.34)
.BottomMargin = Application.InchesToPoints(0.55)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.3)
.CenterHorizontally = False
.Orientation = xlPortrait

End With




   
    MsgBox "This invoice has been exported to a new workbook: " & iname2
   
ThisWorkbook.Activate


Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Vishal Srivastava · May 21, 2015

Deepak said:

This will make a copy a sheet to new workbook without any changes.

Sheets("Invoice").Copy

now do the further.

projected code

Code:

Sub Export_Invoice()
Application.ScreenUpdating = False
Application.DisplayAlerts = False



  
    Dim iname As Variant
    Dim iname2 As Variant
    Dim d As Variant
    Dim shipid As Integer
    Dim shiptext As String
    Dim iWords As String

Sheets("Invoice").Copy
          
    ActiveSheet.Columns("L:P").EntireColumn.Hidden = True
  
    For Each d In ActiveSheet.Shapes
        If Left(d.Name, 4) = "Drop" Then
            d.Delete
        End If
    Next

  
    ActiveWorkbook.Colors(39) = RGB(234, 234, 234)
    Range("A1").Select
    ActiveWindow.DisplayGridlines = False
    Application.CutCopyMode = False
  

  
With ActiveSheet.PageSetup


  .Zoom = False
  .FitToPagesWide = 1

        .FitToPagesTall = False

.LeftHeader = ""

.CenterFooter = "Page &8&P of &N"

.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.34)
.BottomMargin = Application.InchesToPoints(0.55)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.3)
.CenterHorizontally = False
.Orientation = xlPortrait

End With




  
    MsgBox "This invoice has been exported to a new workbook: " & iname2
  
ThisWorkbook.Activate


Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Hi Deepak,
Excellent.

It works like a charm.

Thanks alot.

But one new problem now I am facing that UDF used in Main File in not working in exported file.

Earlier I made a reference in Main Invoice as:
=Sample1.xlsm!spellnumber(invtotal)

Which worked perfectly in Exported Invoice.

Now using your code Reference now got changed with exported file name e.g: =Book1.xlsm!spellnumber(invtotal)

Any Idea how to make the UDF reference when exporting the Invoice?????

Thanks in Advance.

Deepak · May 21, 2015

Vishal Srivastava said:
Hi Deepak,

Any Idea how to make the UDF reference when exporting the Invoice?????

Thanks in Advance.

Hi,

This is a workaround as there are more ways too like as addin & other ref...

Code:

Dim myrng As Range
Set myrng = ActiveSheet.[C27]
Sheets("Invoice").Copy
myrng.Copy [C27]

Vishal Srivastava · May 21, 2015

Deepak said:
Hi,

This is a workaround as there are more ways too like as addin & other ref...

Code:

Dim myrng As Range Set myrng = ActiveSheet.[C27] Sheets("Invoice").Copy myrng.Copy [C27]

Thanks Deepak.

It works.

Deepak · May 21, 2015

Vishal Srivastava said:
Thanks Deepak.

It works.

Welcome buddy!!

Export Invoice-VBA

Vishal Srivastava

Member

Attachments

Deepak

Excel Ninja

Vishal Srivastava

Member

Deepak

Excel Ninja

Vishal Srivastava

Member

Deepak

Excel Ninja

Vishal Srivastava

Member

Deepak

Excel Ninja

Vishal Srivastava

Member

Deepak

Excel Ninja

Vishal Srivastava

Member

Deepak

Excel Ninja