CODEWITHSUNDEEP
vbaexcel
Alwyn Miranda
Alwyn Miranda

Reputation: 370

Copy - paste issue

I know I may sound really very silly to many of you As I present my issue.

What I am doing is I am trying to copy paste data from for excel sheet to my master excel sheet through a VBA code.

sht1.Sheets(1).Activate
Range("B58:L87").Copy
ThisWorkbook.Sheets("Q1").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

sht2.Sheets(1).Activate
Range("B58:L87").Copy
ThisWorkbook.Sheets("Q2").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

And continue the same for next two sheets. Q3 and Q4.

First time in my life I observed a strange thing which was happening while running the code.

When I run the complete code in one go without breakpoints (F5) -> Q1 and Q4 sheets are blank. Q2 and Q3 sheets are copied successfully.

But When I run the same code step by step execution (F8) -> All four sheets Q1,Q2,Q3 and Q4 are filled with the required data.

could someone please open my eyes to the unseen background process :)

I found the answer today.... That's after so many days :)

I moved the sheet , from 3rd position to the first position. But internally its (Sheet(1) which I didn't took into Consideration.... :)

Upvotes: 0

Views: 86

Answers (3)

Alwyn Miranda
Alwyn Miranda

Reputation: 370

I found the answer today.... That's after so many days :)

I moved the sheet , from 3rd position to the first position. But internally its (Sheet(1) which I didn't took into Consideration.... :)

Upvotes: 0

John Alexiou
John Alexiou

Reputation: 29274

Instead of Copy and Paste, try assigning the values directly

Dim src as Range, dst as Range
Set src = sht1.Range("B58:L87")
Set dst = ThisWorkbook.Sheets("Q1").Range("A1")

dst.Resize(src.Rows.Count,src.Columns.Count).Value = src.Value

Upvotes: 1

SierraOscar
SierraOscar

Reputation: 17647

If it works when de-bugging then it should work at runtime, unless you are selecting workbooks/sheets during the debug.

Try this instead of your current code.

For i = 1 To 4
   ThisWorkbook.Sheets("Q" & i).Range("A1:K30").Value = sht1.Sheets(1).Range("B58:L87").Value
Next i

Upvotes: 1

Related Questions