Formatting and copy or copy and formatting ??

[Howard]

The two macros copy multiple ranges to a single placement or
single range to multiple placements on sheets within the workbook.

I want the option to format the destinations as one may opt for.
In the first macro I chose $, Bold and Italics, just as an example.
Do I format on the "copy from" sheet and do something like paste special format/values??

Or can I add a line or few of code to do the formatting on the "copied to sheets" sheets?

What recommendations do you suggest?

Thanks.
Howard

Option Explicit

Sub CopySheetRangeSToSheets()
'// Copy multiple ranges to a single placement
' on the other sheets

Dim Rng As Range
Dim ShNumBr As Long
Dim i As Long
Set Rng = Range("A1:A9, C1:C9, E1:E9")

ShNumBr = Worksheets.Count
Rng.Copy

For i = 2 To ShNumBr
With Worksheets(i).Range("A1")
.PasteSpecial xlPasteValues
'// And then on the copiy to sheets those ranges formatted
'// Range("A1:A9 as $, C1:C9 as Bold, E1:E9 as Italics")
End With
Next 'i

'Application.CutCopyMode = False
'CopySheetRangeToSheetsRanges
End Sub

Sub CopySheetRangeToSheetsRanges()
'// Copy single range to a multiple placements
' on the other sheets

Dim Rng As Range
Dim ShNumBr As Long
Dim i As Long
Set Rng = Range("B3:C7")

ShNumBr = Worksheets.Count
Rng.Copy

For i = 2 To ShNumBr
With Worksheets(i).Range("K10,M18,D29")
.PasteSpecial xlPasteValues
End With
Next 'i

Application.CutCopyMode = False
End Sub

 

[GS]

Usually, source data sheets are either not formatted or they're
pre-formatted. Thus the target sheets would be pre-formatted for the
expected data, or the data/range formatted as desired after populating
the respective cells. Ultimately, it's up to you or your client how the
target sheet looks!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Howard]

Usually, source data sheets are either not formatted or they're

pre-formatted. Thus the target sheets would be pre-formatted for the

expected data, or the data/range formatted as desired after populating

the respective cells. Ultimately, it's up to you or your client how the

target sheet looks!<g>

Okay, after a bit more thought, seems a fairly simple macro to format say 20 to 35 sheet is really not much of a hurdle.

The Sunday afternoon laziness must have over come me.<g> zzzz

Thanks, Garry, back to your nap too.

Regards,
Howard