LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 34
Default Copy and paste formatting and values only

Whilst there is no error checking and could be cleaned up but this is
what I managed to come up with and is working wonderfully! Except if
there is a \ character in the name of the region......

Sub CreateReports()

Dim i As Integer
Dim CCRange As Range
Dim fname As Variant
Dim bname As Variant


MyPath = "C:\TEMPTRIAL\REPORTS\"
ChDir MyPath

i = 0

' inititate loop in range
For Each c In Range("loop.range")

' Open budget template workbook
Workbooks.Add Template:="C:\TEMPTRIAL\budget worksheet template.XLT"

' return to 2008/09 Master Worksheet
Windows("2008-09 Budget Template - trial.xls").Activate
Worksheets("BA Report").Range("BCostCentre") = c.Value

Calculate

bname = "(" & Worksheets("BA
Report").Range("BCostCentreDescription").Value & ")"

Range("D10:E11").Select
Selection.Copy
Windows("budget worksheet template1").Activate
Range("D10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Windows("2008-09 Budget Template - trial.xls").Activate

Range("G19:H735").Select
Selection.Copy
Windows("budget worksheet template1").Activate
Range("G19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

' BCostCentreDescription


fname = c.Value & " - " & bname & " - 08.09 BUDGET TEMPLATE" &
".xls"
ActiveWorkbook.SaveAs Filename:=fname, CreateBackup:=False
ActiveWorkbook.Close

Next c


End Sub
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy and paste up to values in last row Scott Excel Discussion (Misc queries) 2 September 23rd 09 10:23 PM
Copy and paste values, formatting and formulas Joe M. Excel Discussion (Misc queries) 2 February 29th 08 09:38 PM
Copy/Paste how to avoid the copy of formula cells w/o calc values Dennis Excel Discussion (Misc queries) 10 March 2nd 06 10:47 PM
Copy/Paste Values briank Excel Programming 5 May 27th 05 05:20 PM
How do i compare values from two sheet and copy & paste if values match? rozb Excel Programming 0 March 5th 04 12:06 AM


All times are GMT +1. The time now is 09:18 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"