Data from one spreadsheet to another....

[ExcelNovice]

I'm working with a spreadsheet with data in rows from A to Y and all the way down to column 1872. This is a fairly large spreadsheet. Each row contains important data in each cell, and that depending on certain criteria the data needs to be copied and pasted to another spreadsheet.

Example, if cell Y5 has a value of 1, then cells D5, E5 and the sum of cells P5, R5 & T5 needs to be copied to spreadsheet "Export" in cells B3, C3 & D3 respectively. Can this be done using a macro, and also is it possible for the macro to carry out this function. Any help will be appreciated. Also, can the macro this data for each occurrence of the value 1 in cell column Y, and can the copy and paste function occur in cells B3, C3 & D3, and then B4, C4 & D4 and so on and so on? Thanks for your help.

 

[Norie]

This can be done using a macro.

Are there any other conditions?

Where does the data start?

Dim I As Long
Dim J As Long
Dim rng As Range
J = 3
For I = 5 To 1872
    Set rng = ActiveSheet.Range("Y" & I)
    If rng.Value = 1 Then
        rng.Offset(0, -21).Resize(1, 2).Copy Sheets("Export").Range("B" & J)
        Sheets("Export").Range("D" & J) = rng.Offset(0, -5).Value + rng.Offset(0, -7).Value + rng.Offset(0, -9).Value
        J = J + 1
    End If
Next I

 

[ExcelNovice]

Thanks for your help Norie.
The data starts at A5, and no, there's no additional conditions.

 

[ExcelNovice]

Hi Norie,

I tried the macro and received a debug error: "Cannot change part of a merged cell" but I'm not using any merged cell. The following line is highlighted on the debug:
rng.Offset(0, -21).Resize(1, 2).Copy Sheets("Export").Range("B" & J)


HELP!!

Private Sub CommandButton2_Click()
Dim I As Long
Dim J As Long
Dim rng As Range
J = 3
For I = 5 To 1872
Set rng = ActiveSheet.Range("Y" & I)
If rng.Value = 1 Then
rng.Offset(0, -21).Resize(1, 2).Copy Sheets("Export").Range("B" & J)
Sheets("Export").Range("D" & J) = rng.Offset(0, -5).Value + rng.Offset(0, -7).Value + rng.Offset(0, -9).Value
J = J + 1
End If
Next I
End Sub

 

[Norie]

Are you 100% sure you don't have any merged cells?

When you select all cells, goto Format>Cells... is Wrap text selected on the Alignment tab?

 

[ExcelNovice]

Thanks Norie. It works. I had a merged cell.
One additional question. Your macro works when "Export" is a sheet on the same spreadsheet file, but is it possible to get this to work when "Export" is another spreadsheet file?

Thanks.

 

[Norie]

Yes.

You would just need to reference the other workbook.

rng.Offset(0, -21).Resize(1, 2).Copy Workbooks("Other.xls").Sheets("Export").Range("B" & J)

Note the other workbook must be open.

 

[ExcelNovice]

Thanks Norie....

 

[ExcelNovice]

Nories and others, the macro below works well, but is there a way for each update to add to the content of spreadsheet "Report" instead of replacing the info that's already on sheet?

Private Sub CommandButton2_Click()
Dim I As Long
Dim J As Long
Dim rng As Range
J = 7
For I = 5 To 1872
Set rng = ActiveSheet.Range("Y" & I)
If rng.Value = 1 Then
rng.Offset(0, -21).Resize(1, 2).Copy Workbooks("Report.xls").Sheets("data").Range("B" & J)
Workbooks("Report.xls").Sheets("dara").Range("D" & J) = rng.Offset(0, -5).Value + rng.Offset(0, -7).Value + rng.Offset(0, -9).Value
J = J + 1
End If
Next I
End Sub