copy data from specific sheets based on headers



I  try   copying  data  from  sheets(import,export)  to  sheet inventory   . the  sheet INVENTORY  contains  the  headers .   it  should  match  with sheets (import,export) and  copy  the  data  without  repeatedly  when  run  the  macro continuosly . 

so   this  is  what  I  have   but  it  gives error object required  in this line 

 For i = 0 To UBound(cols)
Sub Copy_Columns()
  Dim cols, sh As Variant, sh2 As Worksheet, i, s As Long, c As Long, f As Range
  Set sh2 = Sheets("INVENTORY")
  sh = Array("export", "import")
  cols = Array("BRAND", "MODEL", "CLIENT", "QTY IMP", "QTY EX") 'Put column titles here
 For s = 0 To UBound(sh)
  For i = 0 To UBound(cols)
    Set f = sh.Rows(1).Find(cols(i), , xlValues, xlWhole)
    If Not f Is Nothing Then
      c = f.Column
      Set f = sh2.Rows(1).Find(cols(i), , xlValues, xlWhole)
      If Not f Is Nothing Then
        sh.Columns(c).Copy sh2.Columns(f.Column)
      End If
    End If
  MsgBox "End"
End Sub



Good try, Kalil, but I think you have aligned your columns so well in your sheets that you don't need to let your code search for matches. The code below will do what you want.

' if you are very good at coding you can omit 'Option Explicit'
' but if you are very good at coding you will never omit it.
Option Explicit

Sub Copy_Columns()
    ' 305

    ' all your variable names are without meaning
    ' the intention must be to make reading your code more difficult:
    ' In this you succeeded, but you were the one most suffering from the deficit.
    ' Dim cols, sh As Variant, sh2 As Worksheet, i, s As Long, c As Long, f As Range

    Dim WsInv   As Worksheet            ' "Inventory"
    Dim WsPort  As Worksheet            ' sheet "Export" or "Import"
    Dim Impex   As Long                 ' loop counter: Import to Export
    Dim Rs      As Long                 ' loop counter: source (Impex)rows
    Dim Rt      As Long                 ' target (Inventory) row

    Set WsInv = Sheets("INVENTORY")
    Application.ScreenUpdating = False

    Set WsPort = Worksheets("Import")
    For Impex = 1 To 2
        With WsPort
            ' start from row 2 to end of column A
            For Rs = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
                .Range(.Cells(Rs, "B"), .Cells(Rs, "E")).Copy
                With WsInv
                    Rt = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
                    .Cells(Rt, "B").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                    If Impex = 2 Then
                        With .Cells(Rt, "E")
                            .Copy Destination:=.Offset(0, 1)
                        End With
                    End If
                    .Range(.Cells(Rt, 1), .Cells(Rt, "F")).Interior _
                           .Color = Array(16247773, 15123099)(Impex - 1)
                End With
            Next Rs
        End With
        Set WsPort = Worksheets("Export")
    Next Impex

    With Application
        .ScreenUpdating = False
        .StatusBar = "Done"         ' this will appear at the bottom left corner
    End With
End Sub


  your  code    copies   data   to  the  bottom  about QTY EX from sheet EXPORT it  should  copy  to    next  to  rest  of  columns  .  it  shouldn't  copy  the  data  again  if  there  is  existed  in sheet IMPORT   just  copy  the  values  to  next  rest  of columns    and  it  shouldn't  copy  to  the  bottom repeatedly when  run the  macro  continously
Kalil (rep: 14) Aug 3, '21 at 3:25 am
Hi Kalil,
The code copies to the worksheet that you provided. There is no evidence in your question or your worksheet that the data should be copied to any other location than the one to which my code copies it.
You did say that you didn't want duplicates. However, this request is diagonally opposite to your code which looks at entire columns. This is the reason why I designed my code to transfer one row at a time. This enables a check for duplication and stop the process row by row if necessary. I missed your information as to whether thee Im- and Export sheets contain periodic additions or whether they are permanent records that constantly grow. In the latter case your entire work flow may have to be revised.
However, your layout doesn't provide for identification of individual entries. You should have an extra column for this in the Inventory sheet as well as the Import and Export sheets. You should design a system by which entries are uniquely identified. Without such ID duplicates can't be determined.
I suggest you revise your workbook design to fit your actual requirements, make sure that you fully visualize your intended work flow, and then formulate a new question based on the improved design, sharing your work flow design as well.
Variatus (rep: 4864) Aug 3, '21 at 9:21 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login