CODEWITHSUNDEEP
excelvba
Gianni
Gianni

Reputation: 1

Excel Vba copy/paste sheets in both directions

enter image description here

I want to write an vba code in Excel to combine multiple sheets. The combine part is working fine. But after that, I want to change the values in sheet "combine" and push the values back to the other sheets. Are there few tips how I can accomplish this?

Here is my script at the moment but the sub PushData is not working very well.. (thats the part to push the values back to their original sheets) Imagine that my sheetname "Doors;Casework;Floors;.." in Excel is in this code "sheet1;sheet2;sheet3;.." .

Thank you!

Option Explicit

Sub CombineSheets()
    Dim wb As Workbook
    Dim ws As Worksheet, wsCombined As Worksheet
    Dim i As Long, j As Long, k As Long, lastrow As Long
    
    Set wb = ThisWorkbook
    Set wsCombined = wb.Sheets("Combine")
    
    'Clear the contents of the combined sheet
    wsCombined.Cells.Clear
    
    'Initialize last row variable to 0
lastrow = 0
    
    'Loop through each sheet to be combined
For k = 1 To 3
    Set ws = wb.Sheets("Sheet" & k)
    
    'Copy the data from the current sheet to the combined sheet
    For i = 1 To ws.UsedRange.Rows.Count
        For j = 1 To ws.UsedRange.Columns.Count
            wsCombined.Cells(lastrow + i, j).Value = ws.Cells(i, j).Value
        Next j
    Next i
    
    'Update the last row variable to the last row of the current sheet

    lastrow = wsCombined.UsedRange.Rows.Count
    
    lastrow = lastrow + 2 ' Add 2 for the empty rows between sheets
    Next k
    
    MsgBox "Sheets combined successfully!"
End Sub

   


Sub PushData()
    Dim wb As Workbook
    Dim ws As Worksheet, wsCombined As Worksheet
    Dim i As Long, j As Long, k As Long, lastrow As Long
    
    Set wb = ThisWorkbook
    Set wsCombined = wb.Sheets("Combine")
    
    'Initialize last row variable to 0
lastrow = 0

    'Loop through each sheet that was combined
    For k = 1 To 3
        Set ws = wb.Sheets("Sheet" & k)
        
        'Copy the data from the combined sheet back to the original sheet
        For i = 1 To ws.UsedRange.Rows.Count
            For j = 1 To ws.UsedRange.Columns.Count
            
                ws.Cells(i, j).Value = wsCombined.Cells(lastrow + i, j).Value
            Next j
        Next i
        lastrow = ws.UsedRange.Rows.Count + 2
    Next k
   
    MsgBox "Data pushed back successfully!"
End Sub

Upvotes: 0

Views: 33

Answers (1)

k1dr0ck
k1dr0ck

Reputation: 1215

Sub pushdata()
    Dim findsheetname As Range
    Dim foundsht As Range
    Dim myArray() As Variant
    Dim i As Long

    myArray = Array("Doors", "Floors", "Casework")
    Set findsheetname = Worksheets("Combine").Columns(1)
    For i = LBound(myArray) To UBound(myArray)
        Set foundsht = findsheetname.Find(what:=myArray(i), LookIn:=xlValues)
        If Not foundsht Is Nothing Then
            foundsht.CurrentRegion.Offset(2).Resize(foundsht.CurrentRegion.Rows.count - 2).Copy
            Worksheets(myArray(i)).Range("A2").PasteSpecial xlPasteValues
        End If
    Next i
    
    Application.CutCopyMode = 0
End Sub

Upvotes: 0

Related Questions