Pastespecial VBA code error

[JawaharPrem]

Hi All,

in my sheet2 range(P6:Z23) dashboard, when i select april radio button the dashboard should paste in same fomate in ("b6) iam not getting proper output while iam running this code please help on same. excel file enclosed for reference..

Sub OptionButton1_Click()
  Rows("6:23").Select
  Selection.Delete Shift:=xlUp
  Columns("a:l").Select
  Selection.Delete Shift:=xlToLeft
  Sheet2.Range("b6").PasteSpecial Paste:=xlPasteAll, Operation:=xlPasteSpecialOperationNone, SkipBlanks:=False _
  , Transpose:=False = Sheet2.Range("P6:Z23").Copy
  Application.CutCopyMode = False
  Columns("b:L").EntireColumn.AutoFit
  Columns("B:L").EntireColumn.AutoFit
  Columns("B:L").EntireColumn.AutoFit
 
End Sub

 

[Hui]

Sub OptionButton1_Click()
  Rows("6:23").Select
  Selection.Delete Shift:=xlUp
  Columns("a:l").Select
  Selection.Delete Shift:=xlToLeft
  Sheet2.Range("P6:Z23").Copy
  Sheet2.Range("b6").PasteSpecial Paste:=xlPasteAll, Operation:=xlPasteSpecialOperationNone, SkipBlanks:=False _
  , Transpose:=False
  Application.CutCopyMode = False
  Columns("b:L").EntireColumn.AutoFit
  Columns("B:L").EntireColumn.AutoFit
  Columns("B:L").EntireColumn.AutoFit

EndSub

 

[JawaharPrem]

Hi,

Thanks for your time i copied your code and pasted but the same error, data has pasted in coloum in "B6" only blank..

 

[Hui]

Can you attach the file ?

 

[NARAYANK991]

Hi ,

Your first 2 lines of code are :

  Rows("6:23").Select
  Selection.Delete Shift:=xlUp

Once you have deleted all the rows from 6 to 23 , what will the range P6:Z23 contain ?

Narayan

 

[Hui]

I'd also check that you are on Sheet2 to start with ?

 

[Hui]

Your code cleaned up should be

  Sheet2.Rows("6:23").Delete Shift:=xlUp
  Sheet2.Columns("A:L").Delete Shift:=xlToLeft
 
  Sheet2.Range("P6:Z23").Copy
  Sheet2.Range("b6").PasteSpecial _
    Paste:=xlPasteAll, _
    Operation:=xlPasteSpecialOperationNone, _
    SkipBlanks:=False, _
    Transpose:=False
  Application.CutCopyMode = False
  Columns("B:L").EntireColumn.AutoFit

 

[NARAYANK991]

Hi ,

The reason the PasteSpecial is failing is because you have merged cells.

Narayan

 

[JawaharPrem]

Hi,

Thanks, but the same code works if the dasboard in sheet3 insteed of sheet2 iam getting error only in sheet2.

Jawahar Prem

 

[Hui]

All the ranges refer to sheet2
Change them to Activesheet