VBA Help

[dazsar]

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

[patel]

can you attach a sample file for testing ?

[HaHoBe]

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