• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Consolidate Sheets as per header


Hi !

As again there in no result worksheet, I can just answer
easy way is an advanced filter, around 20 codelines …
See samples in the threads within this forum.
And could be done without a code !
​
 

Visiting last attachment, advanced filter works only with Sheet1.

For others worksheets, it's just an easy Copy method.
You just have to find out first blank row as we yet showed you
so many times in your past threads !

So you can try yourself and post your code attempt if any issue
and explaining it …
​
 
Marc L said:
Visiting last attachment, advanced filter works only with Sheet1.

For others worksheets, it's just an easy Copy method.
You just have to find out first blank row as we yet showed you
so many times in your past threads !

So you can try yourself and post your code attempt if any issue
and explaining it …
​
Click to expand...
U told me advanced filter work on this data i tried but i am unable to do this headers not same in both sheet but if i can insert one column with same header name then Advanced filter what is code for this please tell me
 

Yes for one by just following VBA help, beginner level …
As you can directly use it from worksheet with Macro Recorder activated.

A sample in this thread from many others …
​
 
Hi

I use this code for matching header then paste but this is for only one sheet data copy paste please tell me how to loop this with all sheet
Code: 
Sub CopyDataBlocks()

'VARIABLE NAME                'DEFINITION
Dim SourceSheet As Worksheet    'The data to be copied is here
Dim TargetSheet As Worksheet    'The data will be copied here
Dim ColHeaders As Range        'Column headers on Target sheet
Dim MyDataHeaders As Range      'Column headers on Source sheet
Dim DataBlock As Range          'A single column of data
Dim c As Range                  'a single cell
Dim Rng As Range                'The data will be copied here (="Place holder" for the first data cell)
Dim i As Integer


'Change the names to match your sheetnames:
Set SourceSheet = Sheets("Target")
Set TargetSheet = Sheets("Source")


With TargetSheet
    Set ColHeaders = .Range(.Cells(1, 1), .Cells(1, .Columns.Count).End(xlToLeft)) 'Or just .Range("A1:C1")
    Set Rng = .Cells(.Rows.Count, 1).End(xlUp).Offset(1) 'Shoots up from the bottom of the sheet untill it bumps into something and steps one down
End With


With SourceSheet
    Set MyDataHeaders = .Range("A1:F1")
   
'Makes sure all the column names are the same:
'Each header in Source sheet must have a match on Target sheet (but not necessarily in the same order + there can be more columns in Target sheet)
    For Each c In MyDataHeaders
        If Application.WorksheetFunction.CountIf(ColHeaders, c.Value) = 0 Then
            MsgBox "Can't find a matching header name for " & c.Value & vbNewLine & "Make sure the column names are the same and try again."
            Exit Sub    'The code exits here if thereäs no match for the column header
        End If
    Next c
   
'There was a match for each colum name.
'Set the first datablock to be copied:
    Set DataBlock = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)) 'A2:A & the last cell with something on it on column A


'Resizes the target Rng to match the size of the datablock:
    Set Rng = Rng.Resize(DataBlock.Rows.Count, 1)


'Copies the data one column at a time:
    For Each c In MyDataHeaders
        i = Application.WorksheetFunction.Match(c.Value, ColHeaders, 0) 'Finds the matching column name
        Rng.Offset(, i - 1).Value = Intersect(DataBlock.EntireRow, c.EntireColumn).Value    'Writes the values
    Next c


'Uncomment the following line if you want the macro to delete the copied values:
'    Intersect(MyDataHeaders.EntireColumn, DataBlock.EntireRow).ClearContents


End With


End Sub
 

Attachments


As source worksheets headers are equal to target worksheet,
you ever don't need to match them !

If you really need that, use MATCH Excel worksheet function
(train first within a formula in a cell) in VBA (Application.Match) …
If does not match, return value is an error than you can check
using IsError VBA function (or via its opposite IsNumeric).

Use Copy VBA function to add data from a second worksheet to target.
If you don't need source cell format, your way with Value is good.

To loop between worksheets, you can either use their index number
or a For Each W In Array("Sheet1", "Sheet2") following with
Worksheets(W). …
As usual, see samples in VBA help and in the threads of this forum.
​
 
Hi Marc L

You are saying is correct but i am not able to do this i tried so many ways
& also i want from selected folder all files pull the data if header not match then skip that file go to next so can u please give me macro for this

Marc L said:
As source worksheets headers are equal to target worksheet,
you ever don't need to match them !

If you really need that, use MATCH Excel worksheet function
(train first within a formula in a cell) in VBA (Application.Match) …
If does not match, return value is an error than you can check
using IsError VBA function (or via its opposite IsNumeric).

Use Copy VBA function to add data from a second worksheet to target.
If you don't need source cell format, your way with Value is good.

To loop between worksheets, you can either use their index number
or a For Each W In Array("Sheet1", "Sheet2") following with
Worksheets(W). …
As usual, see samples in VBA help and in the threads of this forum.
​
Click to expand...
 
You must log in or register to reply here.
Back
Top