Copy from File 1 to File 2

[iashorty]

I am trying to write a macro that copies from File_1 to File_2.
Background: The items to copy are not always in the same place. I have
written a formula to put an X in column A each time I need to start copies.
IF A1 has an X in it then I need to copy from B1, C2, D7 and C30-G30.
These cells are copied to File_2, to Cells A1-A9.
Each time they go down a row in File_2 so that I have a chart when finished
of accumulated data.
File 2 name changes so I use an identifying name called File_2 rather than
an exact windown name.

This is what I have written so far:
Windows(File_2).Activate
Range("A1").Select
X = ActiveCell.Row
Do While X < 65532
If Cells(X, 1) = "" Then
X = X + 1
Else
If Cells(X, 1) = X Then
RangeRC1.Select
Selection.Copy
Windows("File_1.xls").Activate
Sheets("Import").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Windows(File_2).Activate
RangeR1C3.Select
Selection.Copy
Windows("File_1.xls").Activate
Sheets("Import").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
End If
End If
Loop
This of course isn't working. Can someone give me direction?

 

[Joel]

See if this helps. Not sure exactly whatt you are doing. The code below can
easily be modified.


Set ImportSht = Windows("File_1.xls").Sheets("Import")

With Workbooks(File_2)
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
If .Range("A" & RowCount) <> "" Then
ImportRow = ImportSht.Range("A" & Rows.Count).End(xlUp).Row
ImportRow = ImportRow + 1
Set StartCell = ImportSht.Range("A" & ImportRow)
StartCell.Value = .Range("A1")
StartCell.Value.Offset(1, 0) = .Range("B1")
StartCell.Value.Offset(2, 0) = .Range("C2")
StartCell.Value.Offset(3, 0) = .Range("D7")
StartCell.Value.Offset(4, 0) = .Range("C30")
StartCell.Value.Offset(5, 0) = .Range("D30")
StartCell.Value.Offset(6, 0) = .Range("E30")
StartCell.Value.Offset(7, 0) = .Range("F30")
StartCell.Value.Offset(8, 0) = .Range("G30")
End If
End With

 

[iashorty]

I mixed the information. I am sorry for being misleading. The Columns are B
same row, column C one row down, column D 7 rows down and C-G 30 rows down.

 

[Joel]

Is this better?

Set ImportSht = Windows("File_1.xls").Sheets("Import")

With Workbooks(File_2)
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
If .Range("A" & RowCount) <> "" Then
ImportRow = ImportSht.Range("A" & Rows.Count).End(xlUp).Row
ImportRow = ImportRow + 1
StartCell.Value = .Range("A" & ImportRow)
StartCell.Value.Offset(1, 0) = .Range("B" & RowCount)
StartCell.Value.Offset(2, 0) = .Range("C" & (RowCount + 1))
StartCell.Value.Offset(3, 0) = .Range("D" & (RowCount + 7))
StartCell.Value.Offset(4, 0) = .Range("C" & (RowCount + 30))
StartCell.Value.Offset(5, 0) = .Range("D" & (RowCount + 30))
StartCell.Value.Offset(6, 0) = .Range("E" & (RowCount + 30))
StartCell.Value.Offset(7, 0) = .Range("F" & (RowCount + 30))
StartCell.Value.Offset(8, 0) = .Range("G" & (RowCount + 30))
End If
End With

 

[iashorty]

Yes, thank you. Let me try to work with this.

Is this better?

Set ImportSht = Windows("File_1.xls").Sheets("Import")

With Workbooks(File_2)
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
If .Range("A" & RowCount) <> "" Then
ImportRow = ImportSht.Range("A" & Rows.Count).End(xlUp).Row
ImportRow = ImportRow + 1
StartCell.Value = .Range("A" & ImportRow)
StartCell.Value.Offset(1, 0) = .Range("B" & RowCount)
StartCell.Value.Offset(2, 0) = .Range("C" & (RowCount + 1))
StartCell.Value.Offset(3, 0) = .Range("D" & (RowCount + 7))
StartCell.Value.Offset(4, 0) = .Range("C" & (RowCount + 30))
StartCell.Value.Offset(5, 0) = .Range("D" & (RowCount + 30))
StartCell.Value.Offset(6, 0) = .Range("E" & (RowCount + 30))
StartCell.Value.Offset(7, 0) = .Range("F" & (RowCount + 30))
StartCell.Value.Offset(8, 0) = .Range("G" & (RowCount + 30))
End If
End With

 

[iashorty]

I am getting an Compile error message "For without Next". I tried to solve
this on my own but I do not have enough experience.