VBA code doesn't work on sheet I have coded

kitsa

Board Regular
Joined
Mar 4, 2016
Messages
124
Office Version
  1. 365
  2. 2016
Hi Experts,
I have created a commend button in "Sheet1" to action spreadsheet in "Sheet2", but when I run the VBA code, it takes me to "Sheet2" on (Worksheets("Summary").Select), then the next part of the code it run's in "Sheet1". how does this happen and how do I fix this? I'm confused.

VBA Code:
Private Sub SummaryCopy_Click()

Worksheets("Summary").Select
Range("C7:S7").CurrentRegion.Copy
Range("C8:S8").PasteSpecial xlPasteFormulasAndNumberFormats

End Sub
 

Attachments

  • Sheet1 command tab.PNG
    Sheet1 command tab.PNG
    46.3 KB · Views: 4
  • Sheet2 VBA to action.PNG
    Sheet2 VBA to action.PNG
    72 KB · Views: 3

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Try :

VBA Code:
Worksheets("Summary").Activate
 
Upvote 0
Try :

VBA Code:
Worksheets("Summary").Activate
Hi Logit,
your code didn't fix it, came up error as per pics.
Am I missing a step?
 

Attachments

  • Error1.PNG
    Error1.PNG
    61.5 KB · Views: 5
  • Error pop up.PNG
    Error pop up.PNG
    3.6 KB · Views: 4
Upvote 0
What line of code is highlighted in yellow due to the error code ?
 
Upvote 0
What line of code is highlighted in yellow due to the error code ?
I Just ran it again and no error this time but still actions the code in "Sheet1", when it needs to action "Sheet2", basically, in "Sheet1" it copied ("C7:S7") to ("C8:S8")
 

Attachments

  • VBA code to Sheet2.PNG
    VBA code to Sheet2.PNG
    33.9 KB · Views: 6
Upvote 0
VBA Code:
Private Sub SummaryCopy_Click()
    With Worksheets("Summary")
        .Range("C7:S7").CurrentRegion.Copy
        .Range("C8:S8").PasteSpecial xlPasteFormulasAndNumberFormats
    End With
End Sub
 
Upvote 0
VBA Code:
Sub SummaryCopy_Click()

Sheet2.Range("C7:S7").CurrentRegion.Copy
Sheet2.Range("C8:S8").PasteSpecial xlPasteFormulasAndNumberFormats

End Sub
 
Upvote 0
@kitsa
FYI: The reason that the code has been doing what it has, is that code that is in a worksheet module (as yours is) will treat any ranges as being on that worksheet unless the range is specifically referenced to another worksheet - as both the codes in posts 6 & 7 which should work for you.
 
Upvote 0
@kitsa
FYI: The reason that the code has been doing what it has, is that code that is in a worksheet module (as yours is) will treat any ranges as being on that worksheet unless the range is specifically referenced to another worksheet - as both the codes in posts 6 & 7 which should work for you.
Hi Peter,
I don't really understand. With the 2 codes on Post 6 & 7, they do bring me to the sheet, but then the copy high lights (A3:T9), why would it do this if I have specifically requested (C7:S7)? Which causes wrong paste.
 

Attachments

  • Copy error highlighted.PNG
    Copy error highlighted.PNG
    48.9 KB · Views: 3
Upvote 0
Hi Peter,
I don't really understand. With the 2 codes on Post 6 & 7, they do bring me to the sheet, but then the copy high lights (A3:T9), why would it do this if I have specifically requested (C7:S7)? Which causes wrong paste.
All good,
I found the issue. the issue was "CurrentRegion". When I removed this, it fixed it.

VBA Code:
Sub SummaryCopy_Click()

Sheet2.Range("C7:S7").Copy
Sheet2.Range("C8:S8").PasteSpecial xlPasteFormulasAndNumberFormats

End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,226,453
Messages
6,191,135
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top