Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Appending data from one spreadsheet to another using a macro
I have been trying to append same type data from one spreadsheet to another
using a macro but since I never know how many lines of data are in each spreadsheet I am appending it is either not inserting at all (because I am selecting all -- it is indicating the rows are not the same size) or it is only copying the number of rows from the second spreadsheet (not the number of rows from subsequent spreadsheets if they are higher or it copies blank rows). I am sure there is vb code that allows it to insert from one to another on the fly but I can't get it to work. Any ideas would be appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Appending data from one spreadsheet to another using a macro
This worked for me: assume you have two files open concurrently, one
called Source.xls and the other called Target.xls. Enter the word "stop" (no quotes) at the bottom of the rows in the Source spreadsheet, and run this code while the Source.xls sprdsht is in the active window: Sub CopyRows() Do Until ActiveCell.Value = "stop" Selection.EntireRow.Copy Windows("target.xls").Activate ActiveSheet.Paste ActiveCell.Offset(1, 0).Select Windows("source.xls").Activate ActiveCell.Offset(1, 0).Select Loop End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Appending data from one spreadsheet to another using a macro
I appreciate your quick response. I was just wondering how I could have the
source.xls spreadsheet automatically have the first row down from the last cell automatically say "stop" as this will be something several users would be completing. If it were me it would not be a big deal but I just want to make it easier for the user, so they will use the app. Appreciate it. DebP "Dave O" wrote: This worked for me: assume you have two files open concurrently, one called Source.xls and the other called Target.xls. Enter the word "stop" (no quotes) at the bottom of the rows in the Source spreadsheet, and run this code while the Source.xls sprdsht is in the active window: Sub CopyRows() Do Until ActiveCell.Value = "stop" Selection.EntireRow.Copy Windows("target.xls").Activate ActiveSheet.Paste ActiveCell.Offset(1, 0).Select Windows("source.xls").Activate ActiveCell.Offset(1, 0).Select Loop End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Appending data from one spreadsheet to another using a macro
I found another way with the help of Rowan Drummond. Maybe this could be
helpful for you also (and you don't have to have both spreadsheets open at the same time as long as the column headers are the same, it works great. Try something like: Range("A2:D" & Cells(Rows.Count, 1).End(xlUp).Row).Copy "DebP" wrote: I appreciate your quick response. I was just wondering how I could have the source.xls spreadsheet automatically have the first row down from the last cell automatically say "stop" as this will be something several users would be completing. If it were me it would not be a big deal but I just want to make it easier for the user, so they will use the app. Appreciate it. DebP "Dave O" wrote: This worked for me: assume you have two files open concurrently, one called Source.xls and the other called Target.xls. Enter the word "stop" (no quotes) at the bottom of the rows in the Source spreadsheet, and run this code while the Source.xls sprdsht is in the active window: Sub CopyRows() Do Until ActiveCell.Value = "stop" Selection.EntireRow.Copy Windows("target.xls").Activate ActiveSheet.Paste ActiveCell.Offset(1, 0).Select Windows("source.xls").Activate ActiveCell.Offset(1, 0).Select Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Finding repeated data in a excel spreadsheet | Excel Discussion (Misc queries) | |||
import data using macro | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Macro does not run when data refreshed | Excel Discussion (Misc queries) |