Consulting

Results 1 to 3 of 3

Thread: VBA Help

  1. #1
    VBAX Newbie
    Joined
    Oct 2013
    Posts
    1
    Location

    VBA Help

    every month i produce a large amount of statements for my clients.
    the current set up uses a master work sheet that contains the Statement data for all clients. the vb runs through every client produces the statement and copies it to a new workbook and saves it in the appropriate folder.

    however i want to now add an additional tab called invoice and repeat the process so that the new saved work book will contain a statement and invoice tab

    i have created what i wanted the invoice to look like on the statement tab within a range and tried to create a new sheet and copy the range (invoice) on to the new sheet called "invoice" bur have had no success. the formating does not copy and it generally is not running well

    please can someone help
    below is the current code:


    ActiveWorkbook.RefreshAll
    Worksheets("Statement").Activate
    'Collect Cancellation and Chargeables Data
    ActiveSheet.Range("e147:i147").Select
    Selection.Copy
    Worksheets("NewCancel_Charge").Activate
    Cells(Rows.Count, "a").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveSheet.Range("d3:e1000").NumberFormat = "#,##0.00;-#,##0.00"
    'Collect Journal Data
    Worksheets("journalcalc").Activate
    ActiveSheet.Range("b6:e12").Select
    Selection.Copy
    Worksheets("journaldata").Activate
    Cells(Rows.Count, "a").End(xlUp).Offset(2, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveSheet.Range("d4:d2000").NumberFormat = "#,##0.00;-#,##0.00"
    Set wsSheet = Sheets("Invoice")
    If wsSheet Is Nothing Then
    Sheets.Add.Name = "Invoice"
    End If
    Worksheets("Statement").Select
    Worksheets("Statement").Activate
    Worksheets("Statement").Range("b161:f195").Select
    Selection.Copy
    Worksheets("Invoice").Select
    Worksheets("Invoice").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    'Copy the Report  to new workbook
    Worksheets(Array("Statement", "Invoice")).Copy
    'Copy and paste value in the new workbook
    Worksheets("Statement").Select
    Worksheets("Statement").Activate
    Worksheets("Statement").Range("a1:bz10000").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

  2. #2
    VBAX Mentor
    Joined
    Jul 2012
    Posts
    398
    Location
    can you attach a sample file for testing ?

  3. #3
    VBAX Regular HaHoBe's Avatar
    Joined
    Aug 2004
    Location
    Hamburg
    Posts
    89
    Location
    Hi, dazsar,

    PasteSpecial Paste:=xlPasteValues will only paste the values but no formatting which would be xlPasteFormats and must be added as an adiitional coammnd after the original one. Or you use Copy and add the destination right behind (will copy the formats) and make values of any formulas thereafter.

    And you should consider to wrap your code wioth code-tags for better readability. If I had to rate your thread title on a scale from 1 to 10 it would be about -3.

    Ciao,
    Holger

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •