Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

file copy

  Asked By: Lydia    Date: Oct 10    Category: MS Office    Views: 946
  

I am very new to excel VBA programming.I need to write a program
which copies specific data from two diff Excel spreadsheets and
then creates another excel spreadsheet out of it.

Will any body please guide me in this regard. I would really
Appreciate the help.This is kind of SOS.

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Taylor Evans     Answered On: Oct 10

Yes, we can help  you, but you need to give us more detail. Outline exactly what you want to happen.

 
Answer #2    Answered By: Benjamin Simpson     Answered On: Oct 10

I really appreciate that.I receive two excel  spreadsheets in mail every day in to two different folders. I have to get data  from them, from each spreadsheet, diffenrent work books, and then copy  them in to third excel sheet.

 
Answer #3    Answered By: Adalwen Fischer     Answered On: Oct 10

Yes. What data. Where is it? Named ranges? Does this third file  exist or do you need it created automatically? Are you overwriting data  or adding it each time?

Try to spell out exactly what you need, e.g.

Copy data from Sheet1 of WB1.xls range A1:L1 - paste as the last entry of WB3.xls Sheet1 rows A-L. Copy data from Sheet 2 of WB2.xls range A1:L1 and paste again as the last entry of WB3.xls Sheet1 rows A-L.

 
Answer #4    Answered By: Dylan Evans     Answered On: Oct 10

I want to write  a macro which fetches data  from two different workbooks and copies into another third work book which is generated runtime.I tried to record the macro for the procedure which I do manually...and which I have to auto mate now.
here is what I do:
Here is the code i got when I recorded Macro.I used two work book.
1. I opened both work books from which I wanted data, and started recording macro in both of them.Now, from first work book I copied the column i wanted to copy  and then out of same work book opened another work book and pasted stuff in it.
2. From second work book, I copied and pasted one column each from three different work sheets to the new work book i previously opened.
3. I saved my work book as combineddata.xls.
4. closed previous all the work books.


here is the Macro which I recorded:

Here is the Macro I get.I have also attached the file.

Sub FirstMacro()
'
' FirstMacro Macro
' Macro recorded 8/22/2004 by roomies
'
'
Range("R2").Select
Application.WindowState = xlMinimized
Windows("Renewal Sales Dashboard French.xls").Activate
Workbooks.Add
Application.WindowState = xlMinimized
Windows("Renewal Sales Dashboard French.xls").Activate
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Book1").Activate
ActiveSheet.Paste
Windows("Sales Report RQ August.xls").Activate
Sheets("Initial Owner").Select
Range("E4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("B1").Select
ActiveSheet.Paste
Windows("Sales Report RQ August.xls").Activate
Sheets("Second Owner").Select
Range("E4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("C1").Select
ActiveSheet.Paste
Windows("Sales Report RQ August.xls").Activate
Sheets("Combined").Select
Range("E4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Book1").Activate
Range("D1").Select
ActiveSheet.Paste
Windows("Renewal Sales Dashboard French.xls").Activate
End Sub

I have to automate above macro to copy arrays of any length.
Any Idea,
This is kind  of SOS.

 
Answer #5    Answered By: Kerry Wright     Answered On: Oct 10

As I mentioned before, there are a few questions you'll need to answer before we can be much help  to you:

1. "I need to generate third Excel sheet." Where? You're taking date from WB1 which seems to have just one sheet, WB2 which has at least 3 already and WB3 which I infer contains 12, given there is one for each month. Is this sheet in fact in a new, fourth workbook?

2. What happens the day you run this code again when the new sheet already exists? Do you want the old data  overwritten, or added to?

3. You have copied in 4 columns of data (let's day to new columns A, B, C and D) of variable, but equal length and you are now copying in a long row below. Do you want it in the row immediately below the last data in the A:D range or should there be a gap?

4. So far, you are not conducting any operations with the data, simply pasting it in. Is your intention to go on to manipulate the result? If so, how?

5. Is there a reason you want to run this code as an active process rather than simply linking to the data in the other workbooks?

 
Answer #6    Answered By: Miriam Green     Answered On: Oct 10

So yes,
here is the scenario.
First workbook I have to copy  one column.
second three column.
third one line: which has 12 sheets for each month.( i ignore it first to make it simple).

I have to copy paste all that data  in new work book.which will be in the same directory.

2.Yes, new data should be over written. There is no need for previous data.
3. There should be gap for say two rows.
4. There is no intention to manipulate data right now.
5. I don't understand linking data with other workbooks. Actually, I need to automate this process so that even if there is no body  to take care of it, people who need the data will keep recieving this data by outlook mail.

 
Answer #7    Answered By: Alberta Miller     Answered On: Oct 10

Your recorded macro seems to be doing the job so far as it goes and it seems to me that all you need to do to it now is leave your two row gap and paste your additional row in.

This code will put you in column 1 of the row 3 below the last data  in the 4 columns:

Range("A1").CurrentRegion.Select
Selection.Offset(Selection.Rows.Count + 2, 0).Resize(1, 1).Select
You can now paste in your row.

Since there's to be no manipulation of the data, and since you don't want to link data, that should be about all you need.

 
Didn't find what you were looking for? Find more on file copy Or get search suggestion and latest updates.




Tagged: