How to copy “specific” rows from one sheet and paste in to another in an excel using VBA Macros

Question

I have two sheets (sheet 1 and sheet 2). Sheet1 is a subset of sheet2. I have written a macro that compares the headers of two sheets and then if matches, copy all the contents from Sheet 1 to sheet 2. The next requirement is, I have a key column in Sheet1, I now need to paste the contents of sheet 1 to sheet 2, sheet3, sheet 4 based on the key column values. Please find attached the screenshot for details and also please find the code which I have written by taking the help of you guys in the Stack-overflow. I am new to this and need your help. Image.Please Click

Code:

Private Sub CommandButton3_Click()

Application.ScreenUpdating = False

Dim lastrow As Long, header As Range, foundHeader As Range, lCol As Long, srcWS , desWS1 As Worksheet
Set srcWS = Sheets("Sheet1")
Set desWS1 = Sheets("Sheet2")

lastrow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

lCol = desWS1.Cells(1, Columns.count).End(xlToLeft).Column
For Each header In desWS1.Range(desWS1.Cells(1, 1), desWS1.Cells(1, lCol))
    Set foundHeader = srcWS.Rows(2).Find(header, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundHeader Is Nothing Then
        srcWS.Range(srcWS.Cells(2, foundHeader.Column), srcWS.Cells(lastrow, foundHeader.Column)).Copy desWS1.Cells(1, header.Column)
    End If
Next header

lCol = desWS2.Cells(1, Columns.count).End(xlToLeft).Column
**' I am stuck here. Unable to think beyond these two lines after applying the filter**
**Sheets("Sheet1").Cells(1, 1).AutoFilter Field:=7, Criteria1:="Yellow"
Sheets("Sheet1").Cells(1, 1).SpecialCells(xlCellTypeVisible).Select**

    For Each header In desWS2.Range(desWS2.Cells(1, 1), desWS2.Cells(1, lCol))
        Set foundHeader = srcWS.Rows(2).Find(header, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundHeader Is Nothing Then
            srcWS.Range(srcWS.Cells(2, foundHeader.Column), srcWS.Cells(lastrow, foundHeader.Column)).Copy desWS2.Cells(1, header.Column)
        End If
    Next header

Application.ScreenUpdating = True

End Sub

Many thanks for your time and assistance.

OOPS! Apologies if my question did not make you any sense. Please refer to the screenshot. I need to copy the rows (Based on a key) from sheet1 and paste under appropriate columns of the sheet2. — Vikram N Ganiga

FullStackDeveloper FullStackDeveloper · Accepted Answer · 2020-09-22T17:32:06

Not my work so won't even pretend, but have you tried this?

Credit: https://www.excelcampus.com/vba/copy-paste-cells-vba-macros/

    Sub Range_Copy_Examples()
'Use the Range.Copy method for a simple copy/paste

    'The Range.Copy Method - Copy & Paste with 1 line
    Range("A1").Copy Range("C1")
    Range("A1:A3").Copy Range("D1:D3")
    Range("A1:A3").Copy Range("D1")
    
    'Range.Copy to other worksheets
    Worksheets("Sheet1").Range("A1").Copy Worksheets("Sheet2").Range("A1")
    
    'Range.Copy to other workbooks
    Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Copy _
        Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")

End Sub


Sub Paste_Values_Examples()
'Set the cells' values equal to another to paste values

    'Set a cell's value equal to another cell's value
    Range("C1").Value = Range("A1").Value
    Range("D1:D3").Value = Range("A1:A3").Value
     
    'Set values between worksheets
    Worksheets("Sheet2").Range("A1").Value = Worksheets("Sheet1").Range("A1").Value
     
    'Set values between workbooks
    Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1").Value = _
        Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Value
        
End Sub

Essentially you trying to do a vlookup it sounds like. This site has helped me in the past as well.

https://powerspreadsheets.com/excel-vba-vlookup/

VLookupResult = WorksheetFunction.vlookup(LookupValue, Worksheet.TableArray, ColumnIndex, False)

How to copy “specific” rows from one sheet and paste in to another in an excel using VBA Macros

1 Answers