PasteSpecial not working properly

[robbie19]

Hallo everyone,

I have a problem with a pasteSpecial function in my macro. I want to copy a few Excel-sheets from one workbook to the other. For the first sheet it works fine, but for the second one not. When I debugg, I see that by "copy"-order the sheet I want is actually copied (it gets highlighted in a specific way) but then by "paste" only an empty range is pasted to my second workbook.
What's more, when I remove the xlPasteValues option, the formating of the original sheet does get pasted, but the numbers and formulea not.

This is my code (the relevant part of it):

What the code should do: first copy the A-sheet and then copy the content of the B-sheet for each of the scenarios (as sheets B_1, B_2, B_3).

<code class="vb spaces"></code><code class="vb comments">' wkb - the new created workbook</code>

<code class="vb spaces"> </code><code class="vb comments">' orgWkb - the existing workbook, that performs calculations (not the one that contains the macro, but is already opened) </code>
<code class="vb spaces"> </code>
<code class="vb spaces"> </code><code class="vb plain">relevantSheets = Array(</code><code class="vb string">"A"</code><code class="vb plain">, </code><code class="vb string">"B"</code><code class="vb plain">)</code>
<code class="vb spaces"> </code><code class="vb plain">scenarioList = Array(</code><code class="vb string">"1"</code><code class="vb plain">, </code><code class="vb string">"2"</code><code class="vb plain">, </code><code class="vb string">"3"</code><code class="vb plain">)</code>

<code class="vb spaces"> </code><code class="vb comments">' a flag indicating if the SKV-Sheet is already present in the new workbook.</code>
<code class="vb spaces"> </code><code class="vb keyword">Dim</code> <code class="vb plain">Bexists </code><code class="vb keyword">As</code> <code class="vb keyword">Boolean</code>
<code class="vb spaces"> </code><code class="vb plain">Bexists = </code><code class="vb keyword">False</code>

<code class="vb spaces"> </code><code class="vb keyword">For</code> <code class="vb keyword">Each</code> <code class="vb plain">Scenario </code><code class="vb keyword">In</code> <code class="vb plain">scenarioList</code>
<code class="vb spaces"> </code>
<code class="vb spaces"> </code><code class="vb comments">' activate the original workbook</code>
<code class="vb spaces"> </code><code class="vb plain">Workbooks(orgWkb.Name).Activate</code>
<code class="vb spaces"> </code>
<code class="vb spaces"> </code><code class="vb comments">' set the scenario and recalculate the workbook to fill in the input values.</code>
<code class="vb spaces"> </code><code class="vb plain">Sheets(</code><code class="vb string">"SKV"</code><code class="vb plain">).Range(</code><code class="vb string">"C37"</code><code class="vb plain">) = Val(Scenario)</code>
<code class="vb spaces"> </code><code class="vb plain">Application.CalculateFull</code>
<code class="vb spaces"> </code>
<code class="vb spaces"> </code><code class="vb keyword">Dim</code> <code class="vb plain">NewSheet </code><code class="vb keyword">As</code> <code class="vb plain">Worksheet</code>
<code class="vb spaces"> </code><code class="vb keyword">For</code> <code class="vb keyword">Each</code> <code class="vb plain">relevantSheet </code><code class="vb keyword">In</code> <code class="vb plain">relevantSheets</code>
<code class="vb spaces"> </code><code class="vb plain">Workbooks(orgWkb.Name).Activate</code>
<code class="vb spaces"> </code>
<code class="vb spaces"> </code><code class="vb keyword">If</code> <code class="vb plain">(StrComp(</code><code class="vb keyword">CStr</code><code class="vb plain">(relevantSheet), </code><code class="vb string">"A"</code><code class="vb plain">, vbTextCompare) = 0) </code><code class="vb keyword">And</code> <code class="vb keyword">Not</code> <code class="vb plain">Bexists </code><code class="vb keyword">Then</code>
<code class="vb spaces"> </code><code class="vb keyword">Set</code> <code class="vb plain">NewSheet = wkb.Sheets.Add</code>
<code class="vb spaces"> </code><code class="vb plain">NewSheet.Name = </code><code class="vb string">"A"</code>
<code class="vb spaces"> </code><code class="vb plain">SKVexists = </code><code class="vb keyword">True</code>
<code class="vb spaces"> </code><code class="vb keyword">ElseIf</code> <code class="vb plain">(StrComp(</code><code class="vb keyword">CStr</code><code class="vb plain">(relevantSheet), </code><code class="vb string">"B"</code><code class="vb plain">, vbTextCompare) = 0) </code><code class="vb keyword">And</code> <code class="vb plain">Bexists </code><code class="vb keyword">Then</code>
<code class="vb spaces"> </code><code class="vb keyword">GoTo</code> <code class="vb plain">EndLoop</code>
<code class="vb spaces"> </code><code class="vb keyword">ElseIf</code> <code class="vb plain">(StrComp(</code><code class="vb keyword">CStr</code><code class="vb plain">(relevantSheet), </code><code class="vb string">"B"</code><code class="vb plain">, vbTextCompare) = 0) </code><code class="vb keyword">Then</code>
<code class="vb spaces"> </code><code class="vb keyword">Set</code> <code class="vb plain">NewSheet = wkb.Sheets.Add</code>
<code class="vb spaces"> </code><code class="vb plain">NewSheet.Name = </code><code class="vb string">"B_"</code> <code class="vb plain">& </code><code class="vb keyword">CStr</code><code class="vb plain">(Val(Scenario)</code>
<code class="vb spaces"> </code><code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb spaces"> </code><code class="vb keyword">With</code> <code class="vb plain">Worksheets(relevantSheet)</code>
<code class="vb spaces"> </code><code class="vb plain">.UsedRange.Copy</code>
<code class="vb spaces"> </code><code class="vb plain">NewSheet.UsedRange.PasteSpecial </code><code class="vb comments">' xlPasteValues, _</code>
<code class="vb spaces"> </code><code class="vb comments">' Operation:=xlNone, SkipBlanks:=True, Transpose:=False</code>
<code class="vb spaces"> </code><code class="vb keyword">End</code> <code class="vb keyword">With</code>
<code class="vb plain">EndLoop:</code>
<code class="vb spaces"> </code><code class="vb keyword">Next</code> <code class="vb comments">' end loop over relevant sheets.</code>
<code class="vb spaces"> </code><code class="vb keyword">Next</code> <code class="vb comments">' end loop over scenarios.</code>
<code class="vb spaces"> </code>
<code class="vb spaces"> </code><code class="vb comments">' delete unnecessary sheets of the new file, save and close it.</code>
<code class="vb spaces"> </code><code class="vb plain">wkb.Sheets(</code><code class="vb string">"Tabelle1"</code><code class="vb plain">).Delete</code>
<code class="vb spaces"> </code><code class="vb plain">wkb.Save</code>
<code class="vb spaces"> </code><code class="vb plain">wkb.Close</code>

<code class="vb keyword">End</code> <code class="vb keyword">Sub

</code>

I'll be greatfull for any help!

 

[Jonmo1]

Ok, you have this

Set NewSheet = wkb.Sheets.Add
NewSheet.Name = "B_" & CStr(Val(Scenario)
End If
With Worksheets(relevantSheet)
.UsedRange.Copy
NewSheet.UsedRange.PasteSpecial

NewSheet doesn't have a used range, because it was just created.

Try

Set NewSheet = wkb.Sheets.Add
NewSheet.Name = "B_" & CStr(Val(Scenario)
End If
With Worksheets(relevantSheet)
.UsedRange.Copy
NewSheet.Range("A1").PasteSpecial xlPasteValues

 

[robbie19]

Hi JonMo1, thank's for your answer. It makes sense, but it works somehow with UsedRange as well.

I found the "problem" now. And it's embarrassing to say what it was: the lines in the original Excel start with 31, and when I pasted this to the new sheet the content has landed somehow lower , so that I haven't seen anything on my screen Sorry...