Appends the contents to the respective columns

[sindhuja]

hi,

I have a sheet named "Sample" and "Data". Sample is the master datasheet where i used to append the datas from the DATA sheet to the corresponding columns in the SAMPLE sheet.

Datas in the DATA sheet varies daily. for ex in the sample sheet the below column names are standard ones.

Anu |Asha |Chris | janu | NPO| PO | Shalu| vani| surya| swami

Whereas in the DATA sheet it depends the 1st name may be ASHA, then Janu.... Asha data from data column to be entered in the asha column of sample sheet....

This i was doing manually. Can this be done using macros... if so help me out in this please.

thanks in advance !
-Sindhuja

[mdmackillop]

[VBA]
Option Explicit
Sub CopyData()
Dim Rng As Range
Dim Rw As Long, Col As Long
Dim i As Long, j As Long
With Sheets("data")
'Find last cell
Col = Cells(1, Columns.Count).End(xlToLeft).Column
Rw = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range(Cells(1, 1), Cells(Rw, Col))
'Loop though rows and columns
For i = 2 To Rng.Rows.Count
Rw = Sheets("Sample").Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Sample").Cells(Rw, 1) = .Cells(i, 1)
For j = 2 To Rng.Columns.Count
'Find column for data
Col = Sheets("Sample").Rows("1:1").Find(.Cells(1, j), lookat:=xlWhole).Column
Sheets("Sample").Cells(Rw, Col) = .Cells(i, j)
Next j
Next i
End With
'Format cells
With Sheets("Sample")
.Range("A2:K2").Copy
Range(.Cells(2, 1), .Cells(Rw, 11)).PasteSpecial Paste:=xlPasteFormats
.Activate
.Range("A1").Select
End With

Application.CutCopyMode = False

End Sub

[/VBA]

[sindhuja]

Thanks for the quick Response md....

-Sindhuja

[mdmackillop]

As it's a quiet day
[VBA]
Sub CopyData2()
Dim Source As Range
Dim tgt As Range
With Sheets("data")
Set Source = Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
With Sheets("sample")
Set tgt = .Cells(Rows.Count, 1).End(xlUp).Offset(1)
End With
Source.Copy tgt
Set tgt = tgt.Offset(, 1).Resize(Source.Rows.Count, 10)
With tgt
tgt.FormulaR1C1 = _
"=OFFSET(data!R1C1,MATCH(RC1,data!C1,0)-1,MATCH(R1C,data!R1,0)-1,1,1)"
.Offset(-1).Resize(1, 10).Copy
.PasteSpecial xlPasteFormats
.Copy
.PasteSpecial xlPasteValues
.SpecialCells(xlCellTypeConstants, 16).ClearContents
End With
Sheets("sample").Activate
Range("A1").Select
End Sub
[/VBA]

[sindhuja]

Hi md,

Just a query! Both the codes given above produce the same results...
wat makes the difference between those two..

-Sindhuja

[mdmackillop]

The first moves the data one cell at a time. The second populates the Sample sheet with formulae to return the values, then tidies up.
Try stepping through both codes with Sample worksheet open.

[sindhuja]

Great ! Thanks for the info Md...

-Sindhuja