Copy and paste values with the same sheet names

Question

I have a VBA program that needs to copy and paste values depending on what their sheet names are.
The sheet names will be extracted on a specific column (Bin column) and will be the name basis of the added worksheets. Is there a way that I can copy the values and paste them according to their Bin values?

For example, my bin values are QWE, RTY, UIO where they are duplicated on the original sheet that's why I created a list. Then after creating the list, Sub CreateSheets() will create worksheet QWE, worksheet RTY and worksheet UIO. My problem is how can I paste the values depending on the worksheet names and set number.

sample data sheet

This is the code where I extract and create a list (removing the duplicate values) of the bins

Sub BIN_Values_List()
 
Dim rSelection As Range
Dim ws As Worksheet

Range("F3:F" & Range("F" & Rows.Count).End(xlUp).Row).Select
    
    Set rSelection = Selection
    Set ws = Worksheets.Add
    ws.Name = "BIN LIST"
    
    rSelection.Copy
    
    With ws.Range("A1")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteValues
    End With
    
    
    ws.UsedRange.RemoveDuplicates Columns:=Array(1), Header:=xlGuess
    
    On Error Resume Next
    ws.UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlShiftUp
    On Error GoTo 0
    
    ws.Columns("A").AutoFit
    
    Application.CutCopyMode = False
        


End Sub

After creating the list, this code will create and rename the sheets based on the bin list together with the result template.

Sub CreateSheets()
 
lastcell = ThisWorkbook.Worksheets("BIN LIST").Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lastcell

With ThisWorkbook

newname = ThisWorkbook.Worksheets("BIN LIST").Cells(i, 1).Value

.Sheets.Add after:=.Sheets(.Sheets.Count)

ActiveSheet.Name = newname

Sheets("RESULT TEMPLATE").Range("A1:E205").Copy
ActiveSheet.Paste

End With

Next

ThisWorkbook.Worksheets("SHEET1").Activate
ThisWorkbook.Worksheets("SHEET1").Cells(1, 1).Select

End Sub

Variatus Variatus · Accepted Answer · 2021-04-21T07:08:29

This appears to be the scheme you want to encode:-

"Loop through all rows in the worksheet "BIN LIST" and copy each item to the sheet whose name is indicated by the "BIN" column. If the sheet doesn't exist, add it to the workbook."

You already have a loop that calls up each row but you need to repurpose it to do what's needed which is to copy the data and paste to the other sheet. Use the function below to get the sheet. It will create the sheet, under the proper name, if it doesn't exist. I made it Private because it will be called by your main procedure which is probably Public.

Private Function CurrentSheet(ByVal SheetName As String) As Worksheet

    Dim Fun         As Worksheet            ' function return object
    Dim AppStatus   As Boolean              ' current setting of ScreenUpdating
    Dim Ws          As Worksheet            ' ActiveSheet

    With ThisWorkbook
        On Error Resume Next
        Set Fun = Worksheets(SheetName)
        If Err Then
            ' the error that occured results from the sheet not being available
            With Application
                AppStatus = .ScreenUpdating ' remember current setting
                .ScreenUpdating = False     ' disable updating
            End With
            Set Ws = ActiveSheet            ' remember the current ActiveSheet
            
            ' this will create a new sheet and activate it
            Set Fun = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
            Fun.Name = SheetName
            
            ' return the status to what it was
            Ws.Activate
            Application.ScreenUpdating = AppStatus
        End If
    End With
    Set CurrentSheet = Fun
End Function

And, even more important, you absolutely must refrain from activating sheets and selecting anything. You can read from and write to any sheet, whether it's active or visible or not. Your effort should be directed toward keeping the sheet active that was active when the user started the macro.

The above function will be called from your loop, taking the argument it needs (the sheet name) from the BIN LIST. But for the purpose of testing you can use this little procedure, as did I.

Sub Test_CurrentSheet()

    Dim Ws      As Worksheet
    
    Set Ws = CurrentSheet("QWE")
    Debug.Print Ws.Name
End Sub

Copy and paste values with the same sheet names

1 Answers