Trouble Copying/Pasting Worksheet

[mrcyvr]

Hello,

Im having a weird problem and hopefully someone might be able to help. When triggered my macro copies:

Sheet 2 to Sheet

Then Sheet 1 to Sheet 2

It has to be in that order. I wrote my Macro, the problem is when I execute it the Macro isn't working but the catch is, VBE is not giving me any errors. I was hoping someone might be able to provide some insight as to why it isn't working. Here is my code

Option Explicit
Sub First()

Public Function CopySheet2(M1 As String, M2 As String)
Dim M1 As String


Sheets("Sheet2").Range("A2:P18").Copy Destination:=Sheets("Sheet3").Range("A2")
'Copy the data
Sheets("Sheet2").Range("A2:P18").Copy
'Activate the destination worksheet
Sheets("Sheet3").Activate
'Select the target range
Range("A2").Select
'Paste in the target destination
ActiveSheet.Paste
Application.CutCopyMode = False
End Function
Public Function CopySheet1(M1 As String, M2 As String)
Dim M2 As String

Sheets("Sheet1").Range("A2:P18").Copy Destination:=Sheets("Sheet2").Range("A2")
'Copy the data
Sheets("Sheet1").Range("A2:P18").Copy
'Activate the destination worksheet
Sheets("Sheet2").Activate
'Select the target range
Range("A2").Select
'Paste in the target destination
ActiveSheet.Paste
Application.CutCopyMode = False
End Function
End Sub

Thank you all!

 

[mrcyvr]

Sorry there was a typo here

At the top my macro copies
Sheet 2 to Sheet 3
Then sheet 1 to sheet 2

Hello,

Im having a weird problem and hopefully someone might be able to help. When triggered my macro copies:

Sheet 2 to Sheet 3

Then Sheet 1 to Sheet 2

It has to be in that order. I wrote my Macro, the problem is when I execute it the Macro isn't working but the catch is, VBE is not giving me any errors. I was hoping someone might be able to provide some insight as to why it isn't working. Here is my code

Option Explicit
Sub First()

Public Function CopySheet2(M1 As String, M2 As String)
Dim M1 As String


Sheets("Sheet2").Range("A2:P18").Copy Destination:=Sheets("Sheet3").Range("A2")
'Copy the data
Sheets("Sheet2").Range("A2:P18").Copy
'Activate the destination worksheet
Sheets("Sheet3").Activate
'Select the target range
Range("A2").Select
'Paste in the target destination
ActiveSheet.Paste
Application.CutCopyMode = False
End Function
Public Function CopySheet1(M1 As String, M2 As String)
Dim M2 As String

Sheets("Sheet1").Range("A2:P18").Copy Destination:=Sheets("Sheet2").Range("A2")
'Copy the data
Sheets("Sheet1").Range("A2:P18").Copy
'Activate the destination worksheet
Sheets("Sheet2").Activate
'Select the target range
Range("A2").Select
'Paste in the target destination
ActiveSheet.Paste
Application.CutCopyMode = False
End Function
End Sub

Thank you all!

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub First()

Sheets("Sheet2").Range("A2:P18").Copy Destination:=Sheets("Sheet3").Range("A2")
Sheets("Sheet1").Range("A2:P18").Copy Destination:=Sheets("Sheet2").Range("A2")
End Sub

I find it hard to believe that you got no errors, as the code you've supplied should give you a "Compile Error: Expected end sub"

 

[mrcyvr]

Hey there,


Thank you for the welcome Fluff!

Yeah, I found it strange too. Im not an expert with VBA or anything but I figured VBE would at least spit back some sort of error on the code I wrote.

Your code worked. Except with this workbook there is different data in all three sheets, so thats why Im trying to get the script to copy sheet 2 to sheet 3 first
Then as the second step copy sheet 1 over to sheet 2.


I tried it with the code you provided, and it worked, except the data in sheet 1 copied over into sheets 2 & 3. Any ideas why that is?

Hi & welcome to MrExcel
How about

Sub First()

Sheets("Sheet2").Range("A2:P18").Copy Destination:=Sheets("Sheet3").Range("A2")
Sheets("Sheet1").Range("A2:P18").Copy Destination:=Sheets("Sheet2").Range("A2")
End Sub

I find it hard to believe that you got no errors, as the code you've supplied should give you a "Compile Error: Expected end sub"

 

[Fluff]

Do you have any formulae in that range?

 

[mrcyvr]

Hey Fluff,

Thanks for the reply

There are no formulas in the workbook at all. I double checked.

Do you have any formulae in that range?

 

[Fluff]

In that case you have something else going on, as that code will not copy the data from sheet1 to sheet3.
Check that you don't have any worksheet/workbook events.

 

[mrcyvr]

This seems to work, problem solved! Thanks again Fluff!

Sub CopySheet()




Sheets("Sheet2").Range("A2:P18").Copy Destination:=Sheets("Sheet3").Range("A2")
Sheets("Sheet2").Range("A2:P18").Copy
Sheets("Sheet3").Activate
Range("A2").Select
ActiveSheet.Paste



Application.CutCopyMode = False

Sheets("Sheet1").Range("A2:P18").Copy Destination:=Sheets("Sheet2").Range("A2")
Sheets("Sheet1").Range("A2:P18").Copy
Sheets("Sheet2").Activate
Range("A2").Select

ActiveSheet.Paste

Application.CutCopyMode = False
End Sub




[QUOTE="mrcyvr, post: 5139091, member: 435073"]Hey Fluff,

Thanks for the reply
 
There are no formulas in the workbook at all. I double checked.[/QUOTE]