# Data from one spreadsheet to another....

#### ExcelNovice

##### Well-known Member
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.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### Norie

##### Well-known Member
This can be done using a macro.

Are there any other conditions?

Where does the data start?
Code:
``````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

##### Well-known Member
The data starts at A5, and no, there's no additional conditions.

#### ExcelNovice

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
Yes.

You would just need to reference the other workbook.
Code:
``rng.Offset(0, -21).Resize(1, 2).Copy Workbooks("Other.xls").Sheets("Export").Range("B" & J)``
Note the other workbook must be open.

Thanks Norie....

#### ExcelNovice

##### Well-known Member
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

Replies
2
Views
173
Replies
0
Views
290
Replies
2
Views
417
Replies
6
Views
254
Replies
3
Views
673

1,195,582
Messages
6,010,578
Members
441,557
Latest member
Jbest23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back