PDA

View Full Version : Appends the contents to the respective columns



sindhuja
07-20-2008, 04:24 AM
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
07-20-2008, 05:32 AM
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

sindhuja
07-20-2008, 05:49 AM
Thanks for the quick Response md....

-Sindhuja

mdmackillop
07-20-2008, 06:17 AM
As it's a quiet day

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

sindhuja
07-20-2008, 07:07 AM
Hi md,

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

-Sindhuja

mdmackillop
07-20-2008, 07:11 AM
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
07-20-2008, 07:20 AM
Great ! Thanks for the info Md...

-Sindhuja