Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Copy from File 1 to File 2
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 41
Default Copy from File 1 to File 2
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?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Copy from File 1 to File 2
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" wrote:

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?
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 41
Default Copy from File 1 to File 2
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" wrote:

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" wrote:

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?
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Copy from File 1 to File 2
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" wrote:

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" wrote:

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" wrote:

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?
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 41
Default Copy from File 1 to File 2
Yes, thank you. Let me try to work with this.

"Joel" wrote:

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" wrote:

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" wrote:

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" wrote:

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?
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 41
Default Copy from File 1 to File 2
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.


"iashorty" wrote:

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?
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
copy data for match word from one file to another file mishak Excel Worksheet Functions 1 December 2nd 09 02:11 AM
copy the same raws of all sheets from about a 100 file to a new sheet of a book and save the file [email protected] Setting up and Configuration of Excel 0 March 14th 07 02:13 AM
macro to autofilter other file and copy in opened file matthias Excel Programming 4 August 17th 06 10:52 AM
VBA Macro to copy an column from one Excel file into another file Sam Excel Programming 4 July 3rd 05 03:36 AM


All times are GMT +1. The time now is 09:57 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement