VBA - Cell Reference as Worksheet

[cynthi263]

Hello - i have an excel file that has numerous tabs. Each have a name, what i am trying to do is in the main sheet, enter in the tab name so that the info that is copied will paste to the correct tab. see code below - i get an error on the top line. The Tab will change each time i go into the report so i need to be able to enter in my tab name each time and just run the macro.

Sheet2 is my main sheet where the info is at. AE4 is the cell where i have input the tab name

Sub MoveFI()


ws1 = Sheets("Sheet2").Range("AE4").Value
Sheets("Sheet2").Select
Range("A2:B1200").Copy
Sheets("ws1").Select
Range("A2").Select
ActiveSheet.Paste


End Sub

 

[Fluff]

Hi & welcome to MrExcel.
What is the error number & message that you get?

 

[cynthi263]

Run-Time Error '438'
Object doesn't support this property or method

 

[cynthi263]

I adjusted the code a bit and ran it without the first line and specified the ws1 to the name of the tab and it worked fine.

Sub CopyData()


ws1 = Sheets(Sheets("ALL").Range("AE4").Value)


Sheets("ALL").Select
Range("A2:B1200").Copy
Sheets("ws1").Select
Range("A2").Select
ActiveSheet.Paste


End Sub

 

[Fluff]

That code cannot work, try

Sub CopyData()
   Dim Ws1 As Worksheet
   
   Set Ws1 = Sheets(Sheets("All").Range("A4").Value)
   Sheets("All").Range("A2:B1200").Copy
   Ws1.Range("A2").PasteSpecial
End Sub

 

[DanteAmor]

Hello - i have an excel file that has numerous tabs. Each have a name, what i am trying to do is in the main sheet, enter in the tab name so that the info that is copied will paste to the correct tab. see code below - i get an error on the top line. The Tab will change each time i go into the report so i need to be able to enter in my tab name each time and just run the macro.

Sheet2 is my main sheet where the info is at. AE4 is the cell where i have input the tab name

Sub MoveFI()


ws1 = Sheets("Sheet2").Range("AE4").Value
Sheets("Sheet2").Select
Range("A2:B1200").Copy
Sheets("ws1").Select 'You should not bring quotation marks
Range("A2").Select
ActiveSheet.Paste


End Sub

Try this

Sub MoveFI()
    Dim ws1 As String
    
    ws1 = Sheets("Sheet2").Range("AE4").Value
    Sheets("Sheet2").Range("A2:B1200").Copy
    Sheets(ws1).Range("A2").PasteSpecial xlAll
    Application.CutCopyMode = False
End Sub

 

[cynthi263]

this is great, this worked. Thanks so much!!!!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.