Consulting

Results 1 to 3 of 3

Thread: VBA Help

  1. #1

    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 
    
    
    Formatting tags added by mark007

  2. #2
    can you attach a sample file for testing ?

  3. #3
    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
  •