Solved: Copy Paste in arranged manner

[jigar1276]

Hi Experts,

I have 2 excel file. "Report File.xls" is received by me. I am copying the contents from "Report File.xls" to "Data File.xls".

I want the macro which should prepare my "Data File.xls" by copying Date from cell A2, Team name, Name & No. of Errors from "Report File.xls". The number of Teams are not fixed in "Report File.xls", it can be anywhere from 1 to 3. Number of names in each team in "Report File.xls" are also not fixed.

I have attached sample.zip file which contains both the excel files.

Please help. :banghead:

[mikerickson]

I think this might do what you want. This adds to the existing data sheet, without clearing.
Sub test()
Dim xVal As Variant
Dim teamNameCell As Range, teamInfo As Variant
Dim dataSheet As Worksheet

Set dataSheet = Workbooks("Data File.xls").Sheets("sheet1")
Set teamNameCell = Workbooks("Report File.xls").Sheets("sheet1").Range("A4")

Do Until teamNameCell.Value = vbNullString
With teamNameCell.CurrentRegion
With .Offset(2, 1).Resize(.Rows.Count - 2, 2)
teamInfo = .Value
With dataSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(.Rows.Count, 1)
.Value = Date
.Offset(0, 1) = teamNameCell.Value
.Offset(0, 2).Resize(, 2) = teamInfo
End With
End With
End With
Set teamNameCell = teamNameCell.End(xlToRight)
Loop
End Sub

[jigar1276]

Thanks very much Mikerickson,

The code given by you is working perfectly and as per my requirement.

Thanks again for your efferts and interest. Sorry for delayed thanx.

:mbounce: :bigdance2

[mikerickson]

You're welcome.