Copy and paste based on sheet name and cell value

[topi1]

Is there a way to have vba which does the following.
If the text in the cell A1 in the sheet1 says "sheet2", the vba copies column B from the sheet2 and pastes it the column B of the sheet1. And, if the text in the cell A1 in the sheet1 says "sheet3", the vba copies column B from the sheet3 and pastes it the column B of the sheet1. The workbook will have many more sheets than the three sheets I described. But the goal is to paste into column B of the sheet1 data from the sheet with the matching name. Thank you.

 

[skillilea]

I'd recommend some naming for you sheets and probably a dropdown in A1 of all your sheet names.

Something like this:

Public Sub CopyDataBasedOnSheetName()
    Dim sourceName As String
    Dim destinationSheet As Worksheet
    Dim sourceSheet As Worksheet
    
    sourceName = ActiveWorkbook.Sheets("Sheet1").Range("A1").Value
    
    On Error Resume Next
    Set sourceSheet = Sheets(sourceName)
    On Error GoTo 0
    
    If Not sourceSheet Is Nothing Then
        Set destinationSheet = Sheets("Sheet1")
        
        destinationSheet.Columns("B").ClearContents
        sourceSheet.Columns("B").Copy destinationSheet.Columns("B")
    End If
End Sub

 

[topi1]

I'd recommend some naming for you sheets and probably a dropdown in A1 of all your sheet names.

Something like this:

Public Sub CopyDataBasedOnSheetName()
    Dim sourceName As String
    Dim destinationSheet As Worksheet
    Dim sourceSheet As Worksheet
  
    sourceName = ActiveWorkbook.Sheets("Sheet1").Range("A1").Value
  
    On Error Resume Next
    Set sourceSheet = Sheets(sourceName)
    On Error GoTo 0
  
    If Not sourceSheet Is Nothing Then
        Set destinationSheet = Sheets("Sheet1")
      
        destinationSheet.Columns("B").ClearContents
        sourceSheet.Columns("B").Copy destinationSheet.Columns("B")
    End If
End Sub

I'd recommend some naming for you sheets and probably a dropdown in A1 of all your sheet names.

Something like this:

Public Sub CopyDataBasedOnSheetName()
    Dim sourceName As String
    Dim destinationSheet As Worksheet
    Dim sourceSheet As Worksheet
   
    sourceName = ActiveWorkbook.Sheets("Sheet1").Range("A1").Value
   
    On Error Resume Next
    Set sourceSheet = Sheets(sourceName)
    On Error GoTo 0
   
    If Not sourceSheet Is Nothing Then
        Set destinationSheet = Sheets("Sheet1")
       
        destinationSheet.Columns("B").ClearContents
        sourceSheet.Columns("B").Copy destinationSheet.Columns("B")
    End If
End Sub

Thank you so much. Can't wait to try.

 

[topi1]

I'd recommend some naming for you sheets and probably a dropdown in A1 of all your sheet names.

Something like this:

Public Sub CopyDataBasedOnSheetName()
    Dim sourceName As String
    Dim destinationSheet As Worksheet
    Dim sourceSheet As Worksheet
  
    sourceName = ActiveWorkbook.Sheets("Sheet1").Range("A1").Value
  
    On Error Resume Next
    Set sourceSheet = Sheets(sourceName)
    On Error GoTo 0
  
    If Not sourceSheet Is Nothing Then
        Set destinationSheet = Sheets("Sheet1")
      
        destinationSheet.Columns("B").ClearContents
        sourceSheet.Columns("B").Copy destinationSheet.Columns("B")
    End If
End Sub
[/C
[/QUOTE]

I'd recommend some naming for you sheets and probably a dropdown in A1 of all your sheet names.

Something like this:

Public Sub CopyDataBasedOnSheetName()
    Dim sourceName As String
    Dim destinationSheet As Worksheet
    Dim sourceSheet As Worksheet
   
    sourceName = ActiveWorkbook.Sheets("Sheet1").Range("A1").Value
   
    On Error Resume Next
    Set sourceSheet = Sheets(sourceName)
    On Error GoTo 0
   
    If Not sourceSheet Is Nothing Then
        Set destinationSheet = Sheets("Sheet1")
       
        destinationSheet.Columns("B").ClearContents
        sourceSheet.Columns("B").Copy destinationSheet.Columns("B")
    End If
End Sub

Brilliant! Worked exactly as I had hoped. May try to modify it to paste it below last filled cell in the column A. Thank you so much!

 

[skillilea]

Happy to help