Copy from File 1 to File 2

  • Thread starter Thread starter iashorty
  • Start date Start date
I

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?
 
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
 
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.
 
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
 
Yes, thank you. Let me try to work with this.

Joel said:
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
Click to expand...
 
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.
 
Back
Top