-4

Let's say I have 2 different tabs in my excel file. Sheet 1 will have all the master content. Sheet 2 will be a copy of Sheet 1. Please see the picture attach of the content for Sheet 1. I would like Sheet 2 to copy the information from the cells with text and copy over to Sheet 2. Example here

Update:

Please see attachments for error window and code:

   Sub Copy()
Sheets("Sheet1").Range("B2:I95").Copy Destination:=Sheets("Sheet2").Range("B1")
End Sub

Code Error

DM24
  • 1
  • 2
  • From the linked thread: "You only get that error for one reason: the name your provided does not exist in the collection!" – BigBen Aug 24 '22 at 16:35

1 Answers1

0

That's pretty basic stuff. Try:

I have edited for you due to comments below. Put this in your worksheet module.

Private Sub Worksheet_Activate()
Sheets.Add.Name = "Sheet2"
Sheets("Sheet1").Range("B5:D10").Copy Destination:=Sheets("Sheet2").Range("A1")
End Sub
steveP
  • 79
  • 7
  • "Method 'Range' of object '_Worksheet' failed" error message appeared. I put the code into the worksheet code module instead of the individual module. Would anything have to change? – DM24 Aug 24 '22 at 15:43
  • @DM24 - why would you put this in a sheet code module? – BigBen Aug 24 '22 at 15:45
  • @BigBen I would like the copying over to happen without me running a macro manually, but rather copy over simultaneously. Is that a bad approach? – DM24 Aug 24 '22 at 15:49
  • @BigBen If I do it in an individual module, i get subscript out of range error. – DM24 Aug 24 '22 at 15:53
  • Then you either have no sheet named *Sheet1* or no sheet named *Sheet2* in the active workbook, or are missing both. – BigBen Aug 24 '22 at 15:58
  • It depends on exactly what you need to do, do you want to copy sheet 1 to sheet 2 as soon as you click on(activate) sheet 1? If so then in your worksheet module change the declaration to "activate". Also if your getting a "Subscript out of range" that's usually because the sheet your trying to copy does not currently exist, i'm guessing that's sheet 2, so you would need to add sheet 2. Try adding Sheets.Add.Name = "Sheet2" at the beginning of your worksheet module. – steveP Aug 24 '22 at 16:01
  • @steveP I do have Sheet2 created. I have updated the original post with the error and content of the module. – DM24 Aug 24 '22 at 16:14