Copying cells from one worksheet to another.

[kiwi4444]

As you can see in the attachment there are two worksheets in the lower left corner. Worksheet and worksheet2; I'm trying to copying all the cells in the tab "worksheet" and copy them to "worksheet2".

How would I do that?

Thanks.


https://ibb.co/ibQPQq

 

[My Aswer Is This]

This script will copy the sheet and rename it.

Sub Copy_Worksheet()
'Modified 10/25/2018 7:19 PM  EDT
Application.ScreenUpdating = False
Sheets(1).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Me" & Format(Date, "MM-DD-YY")
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

How would I do that?

Try (in a copy of your workbook)

Sub Copy_Data()
  With Worksheets("worksheet").UsedRange
    .Copy Destination:=Worksheets("worksheet2").Range(.Cells(1).Address)
  End With
End Sub

 

[kiwi4444]

Sorry guys none of this is making any sense.

Ok first of all how would I copy the script into the sheet?

Can someone explain this?

Thank you.

 

[Peter_SSs]

Ok first of all how would I copy the script into the sheet?

Can someone explain this?

1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

 

[kiwi4444]

1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Just to be clear step 2

When I go Insert-Module

https://ibb.co/hMS71V

I type in the code below right?

https://ibb.co/fMPLMV


Thanks again.

 

[Peter_SSs]

See red comments below, then give it a try.

Just to be clear step 2

When I go Insert-Module

https://ibb.co/hMS71V This looks correct

I type in the code below right? Yes, but as stated in step 3, I would copy/paste rather than type - less chance of accidental errors

https://ibb.co/fMPLMV

 

[kiwi4444]

Try (in a copy of your workbook)

Sub Copy_Data()
  With Worksheets("worksheet").UsedRange
    .Copy Destination:=Worksheets("worksheet2").Range(.Cells(1).Address)
  End With
End Sub

Try (in a copy of your workbook)

Sub Copy_Data()

  WithWorksheets("worksheet").UsedRange
    .CopyDestination:=Worksheets("worksheet2").Range(.Cells(1).Address)
  End With
End Sub

1)Thanks so basically this code copies the contents of one works heet to another. In other words it copies the contents of the tab called"worksheet" into the tab called "worksheet2"

Am I understanding this correctly?



2) Now I have another question pretty much the same concept. Let's say I have multiple worksheets; and I would like to copy the contains of the first worksheet called "worksheet" into the third worksheet called "worksheet3".

I would copy the code you provided:

Am I understanding this correctly?​

_{^{<strike>
</strike>}}

Sub Copy_Data()

  WithWorksheets("worksheet").UsedRange
    .CopyDestination:=Worksheets("worksheet3").Range(.Cells(1).Address)
  End With
End Sub

EDIT: Sorry for the messy post.

 

[My Aswer Is This]

What is your ultimate goal here?
Are you wanting to make like 10 copies of the same Worksheet?
When you say multiple do you mean multiple copies of the same Worksheet. The script I provide which you never replied to will make a copy to the Worksheet and give it a new name.
Why would that not work?

We could write a script to Make 10 copies of worksheet named Me and give all the copies new names.

And doing it my way would also include all Buttons and other controls in the sheet. Not just the cell values.

 

[Peter_SSs]

1)Thanks so basically this code copies the contents of one works heet to another. In other words it copies the contents of the tab called"worksheet" into the tab called "worksheet2"

Am I understanding this correctly?

Yes, that is exactly what you asked for.

I'm trying to copying all the cells in the tab "worksheet" and copy them to "worksheet2".

How would I do that?

2) Now I have another question pretty much the same concept. Let's say I have multiple worksheets; and I would like to copy the contains of the first worksheet called "worksheet" into the third worksheet called "worksheet3".

I would copy the code you provided:

Am I understanding this correctly?

Sub Copy_Data()
  WithWorksheets("worksheet").UsedRange
    .Copy Destination:=Worksheets("worksheet3").Range(.Cells(1).Address)
  End With
End Sub

Yes, though if you are going to do it as a separate procedure, you would give the macro a different name.

But if worksheet2 and worksheet3 already exist and you want to copy to both you could combine like this

Sub Copy_Data()
  With Worksheets("worksheet").UsedRange
    .Copy Destination:=Worksheets("worksheet2").Range(.Cells(1).Address)
    .Copy Destination:=Worksheets("worksheet3").Range(.Cells(1).Address)
  End With
End Sub

As suggested by MAIT though, if you are wanting to do this multiple times it would be good to know more about what you have and what you are trying to do. For example, your questions have been about copying data from a particular worksheet to other existing worksheets, which is why I wrote my code the way I did. MAIT's code would be creating those other sheets on the fly. Depends really exactly what you have already and what you are wanting to do as to the best way. Unless you spell it out clearly, we are only guessing.