Pasting

[Guest]

I've got some code that won't let me paste because it says that merged cells
have to be identically sized. Anybody know a way around this? The VBA code
copies something from workbook X and pastes it in workbook Y on worksheet YY,
then copies something from workbook Z and also copies it on worksheet YY in
workbook Y. However, the two paste areas do not touch each other. Any
ideas? I'm on Excel 97. Here's the code:

Sub TestingCombination()

Dim a As Worksheet
Dim b As String
Dim c As Range
Dim d As Worksheet
Dim e As Worksheet
Dim f As Long

Workbooks.Add
b = ActiveWorkbook.Name

Workbooks(2).Activate
Worksheets(1).Activate

Do Until ActiveSheet.Name = "RB-I"
Set e = ActiveSheet.Next
e.Activate
ActiveSheet.Range("D9:I24").Copy
Workbooks(b).Worksheets.Add
Workbooks(b).Activate
Set d = Workbooks(b).Worksheets(1)
With d
.Range("A9").PasteSpecial Paste:=xlPasteValues
.Range("A9").PasteSpecial Paste:=xlPasteFormats
End With
Application.CutCopyMode = False
Set c = Selection
With c
.Columns.AutoFit
.Rows.AutoFit
End With
Workbooks(2).Activate
Loo
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

f = (Workbooks(b).Worksheets.Count - 3)
Workbooks(3).Activate
Worksheets(1).Activate

Do Until ActiveSheet.Name = "CB-I"
Set e = ActiveSheet.Next
With e
.Activate
End With
ActiveSheet.Range("D9:I24").Copy
Workbooks(b).Activate
With Worksheets(f)
.Range("H9").PasteSpecial Paste:=xlPasteValues
.Range("H9").PasteSpecial Paste:=xlPasteFormats
End With
Application.CutCopyMode = False
Set c = Selection
With c
.Columns.AutoFit
.Rows.AutoFit
End With
f = ActiveSheet.Previous
Workbooks(3).Activate
Loop

End Sub

 

[Jim Rech]

This problem may not occur if you use xlPasteFormulas instead of
xlPasteValues. If your source range is composed of constants then the
result will be the same as your current macro. If your source range has
formulas in it then you'll have to add a step to convert them to values
after the paste.

--
Jim Rech
Excel MVP
| I've got some code that won't let me paste because it says that merged
cells
| have to be identically sized. Anybody know a way around this? The VBA
code
| copies something from workbook X and pastes it in workbook Y on worksheet
YY,
| then copies something from workbook Z and also copies it on worksheet YY
in
| workbook Y. However, the two paste areas do not touch each other. Any
| ideas? I'm on Excel 97. Here's the code:
|
| Sub TestingCombination()
|
| Dim a As Worksheet
| Dim b As String
| Dim c As Range
| Dim d As Worksheet
| Dim e As Worksheet
| Dim f As Long
|
| Workbooks.Add
| b = ActiveWorkbook.Name
|
| Workbooks(2).Activate
| Worksheets(1).Activate
|
| Do Until ActiveSheet.Name = "RB-I"
| Set e = ActiveSheet.Next
| e.Activate
| ActiveSheet.Range("D9:I24").Copy
| Workbooks(b).Worksheets.Add
| Workbooks(b).Activate
| Set d = Workbooks(b).Worksheets(1)
| With d
| .Range("A9").PasteSpecial Paste:=xlPasteValues
| .Range("A9").PasteSpecial Paste:=xlPasteFormats
| End With
| Application.CutCopyMode = False
| Set c = Selection
| With c
| .Columns.AutoFit
| .Rows.AutoFit
| End With
| Workbooks(2).Activate
| Loop
|
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
|
| f = (Workbooks(b).Worksheets.Count - 3)
| Workbooks(3).Activate
| Worksheets(1).Activate
|
| Do Until ActiveSheet.Name = "CB-I"
| Set e = ActiveSheet.Next
| With e
| .Activate
| End With
| ActiveSheet.Range("D9:I24").Copy
| Workbooks(b).Activate
| With Worksheets(f)
| .Range("H9").PasteSpecial Paste:=xlPasteValues
| .Range("H9").PasteSpecial Paste:=xlPasteFormats
| End With
| Application.CutCopyMode = False
| Set c = Selection
| With c
| .Columns.AutoFit
| .Rows.AutoFit
| End With
| f = ActiveSheet.Previous
| Workbooks(3).Activate
| Loop
|
| End Sub
|
|
|