Re: Copy Data Range From 1 Workbook To Another
Xlite,
Sorry that I didn't get to thank you earlier for your help. I was away from work for the past 3 weeks. The code works perfectly!
Thanks very much.
Cheers,
Nadia
Re: Copy Data Range From 1 Workbook To Another
Xlite,
Sorry that I didn't get to thank you earlier for your help. I was away from work for the past 3 weeks. The code works perfectly!
Thanks very much.
Cheers,
Nadia
Re: Copy Data Range From 1 Workbook To Another
Xlite,
Many thanks to u, again! Will look at it again next week. Also, will try to understand what each line of the codes means so that I can learn from you. :cool:
Have a great weekend. :music:
Cheers,
Nadia
Re: Copy Data Range From 1 Workbook To Another
Xlite,
Thanks for your reply & advice. I'll try to get a copy of the dummies. Anyway, the codes didn't work for my actual workbooks. :crying: Do you mind to help me test the codes on the actual workbooks please?
I have attached a few sample workbooks here; have a go & let me know.
Thanks very much.
Cheers,
Nadia
Re: Copy Data Range From 1 Workbook To Another
Xlite,
Thanks for the codes. I will try it out & let u know of the progress when I go back to work next week.
Just another question.. What's the likelihood of picking up vba yourself?
Anyway, have a good weekend.
Cheers,
Nadia
Re: Copy Data Range From 1 Workbook To Another
Xlite,
I've 2 problems using the codes to copy the data range from the actual source workbooks to destination workbook.
It was working perfectly fine when I used it to copy a data range from various test workbooks to the destination workbook which I had created for the testing purpose.
Problem 1:
When I ran the macro on the actual workbooks, I got an error message "Subscript out of range". I don't understand why the outcome should be any different from running it on test workbooks.
Problem 2:
The data range to be copied from each workbook is different. I just realised that the cells to be copied are not necessarily saved in B8:R8 in each workbook. They may be saved in different row but the same cols. How do I set a condition for the lookup?
Eg. Copy Br:Rr only when Ar contains the word UK; r stands for row number.
Can this be implemented?
Would you please have another look at the codes and help me with the 2 problems I have?
Thanks very much.
Cheers,
Nadia
Re: Copy Data Range From 1 Workbook To Another
Xlite,
You can ignore the message I just posted. It was a silly mistake I made. It is working!!
Thanks veeeeeeeeeeeeery much for the codes.
Cheers,
Nadia
Re: Copy Data Range From 1 Workbook To Another
Xlite,
I hope you are keeping well. I've tried the 2 segments of the codes u posted on this thread. Unfortunately, it says "no matching file" when I ran it. I've defined FileSpec in my destination workbook which contains the macro.
I don't see any reason why there is no matching files. Can you please help me to look at/test out the codes again?
Do I need to define or do anything about the following:
1. FileSpec
2. Lbound and Ubound
3. FileArray
4. Filename (Does this name define the range of file to look for in the specified dir?)
Please help.
Thanks.
Cheers,
Nadia
Re: Copy Data Range From 1 Workbook To Another
Xlite,
Thanks soooooooooooooooooooooooo much. I truly appreciate your help not only in the coding but also in the comments. The comments have helped me to understand what each line of the code does.
I haven't tried out the codes yet as I will need to get back to the office to do that.
Your offer to help & explain is just something which makes my day & possibly many other newbies' in the forum. I'm rather surprised & impressed with the number of helpful people like yourself in the forum.
Keep up the good work, Xlite!
Thanks again.
Cheers,
Nadia
Re: Copy Data Range From 1 Workbook To Another
Xlite,
Thanks for the codes, but I don't really know how to adapt the codes. I am an accountant and no programmer at all. In fact, I am a beginner who is trying to pick up vba by myself recently. I just need the data to perform my analysis.
Have I missed something? I don't see any code segment which involves pasting the data range copied from the source workbooks to columns next to A on sheet 1 in output.xls.
Please help me if you can. :confused:
Thanks very much.
Regards,
Nadia
Re: Copy Data Range From 1 Workbook To Another
There is a correction to the previous post:
The data range B8:R8 from each source file will need to be copied to Col B to R next to source workbook name in output.xls.
Eg, In output.xls (Sheet 1):
col A col B.......................... col R
sales_10-09-06 50 20
sales_10-10-06 70 35
sales_10-11-06 30 20
I hope this will make things clearer.
Thanks.
Hi there.
Can anyone please help me with the following exercise?
I need to copy the same data range from Sheet 1 of different workbooks in the same subdir to output.xls (also in the same subdir).
In workbook output.xls, I've defined the range of workbooks (source files) as name filenames. I need to copy the same data range (say B8:R8) from each source file to the cells adjacent to the filenames in output.xls.
Eg. I have defined the range of files to copy data from as filenames in Col A of Sheet 1 in output.xls.
Col A
sales_10-09-06
sales_10-10-06
sales_10-11-06
Workbook sales_10-09-06 contain sales data of various product to different firms as below:
Col A Col B Col C .................................. Col R
prod A prod B prod q
Firm 1 50 20 100
Firm 2 20 25 80
Firm 3 30 50 20
How do I copy B8:R8 of sales recorded for each day in sales_date.xls to Col B of Sheet 1 in output.xls? There are 200 files (specified by the name filenames in output.xls) that I need to copy B8:R8 from.
Urgent help is needed and appreciated.
Thanks in advance.
Regards,
Nadia
Re: Paste charts from 1 workbook to another
Hi Carl,
I ran the macro in Importchart.xls just a while ago. It did open the source & destination workbook, but it didn't copy Chart1 from source to destination. Please see attached sample files (samplessheet.xls & empty.xls).
Is there any way to specify the macro to do the following:
1. Prompts for source & destination workbooks with paths (which is done in importchart.xls)
2. Prompts for chart to be copied
3. Prompts for range on the worksheet in destination workbook to be copied to.
4. Copy & resize chart
For example, copy Chart1 from samplessheet.xls to $A$1:$P$25 on Sheet1 in empty.xls.
Thanks again for your help.
Cheers,
Nadia
Re: Automatic chart update with new data entry
Thanks for the help, Krishnakumar! It works perfect for me.
Hi all,
I've been updating all the charts manually each time I update the entries in the worksheet which charts are created from in the same workbook. Is there any way (eg. by use of macros or VBA) that I can have all the charts updated automatically when data ranges are updated each time?
Your help is very much appreciated & thanks in advance.
Cheers
Nadia
Re: Paste charts from 1 workbook to another
Thanks carl!
I need to copy 1 chart from a worksheet (say chart1) in 1 source workbook to the a worksheet (say OT) in 1 destination workbook. The problem is the source workbook is saved as a different name each time the workbook is updated.
Eg. When the data clerk updated the chart in source workbook on Apr 7 2006 & Apr 8 2006, he saved the workbook as wkbook_apr7_2006.xls & wkbook_apr8_2006.xls respectively. So I'll have to copy the chart from wkbook_apr7_2006 & wkbook_apr8_2006 each time I update my destination workbook for analysis, depending on which day's chart I need for the analysis.
My question is, is there a way I can specify the macro to do the following?
1. prompt for the source & destination workbooks
2. specify chart to be copied from the source
3. specify area to be copied to in the destination
4. copy chart from source to the area specified in the destination workbook
It may help if I answer the questions in your previous post as follows:
1. It is one chart sheet from one source workbook to one destination
2. Eg. chart 1 in "source" goes to top half of chart2 (chart sheet) in "destination"
3. copy the data too.
I truly appreciate you taking time to help me out with this.
Thanks in advance.
Cheers,
Amadea
Hi all,
I've been updating charts from different workbooks to a destination workbook manually & it's getting a little too much recently.
Can anyone please help me on writing a macro which prompts for the source & destination workbooks and to copy charts from source to destination workbooks?
Thanks in advance.
Cheers,
Nadia
Re: Deleting unwanted columns
Thanks norie! The program works fine for me. You just don't know how much I appreciate your help... I'm trying to pick up VBA myself just recently. Hopefully, I'll be able to help other newbies like myself one day.
Thanks again to you & royUK.
Cheers,
Nadia
Re: Deleting unwanted columns
Thanks royUK.
I think I didn't describe the problem as clearly as I should. The headings are not quite exactly like that, but they consist words as seen in my previous post. Is there anyway that I can write a macro to delete the 3 consecutive columns after a column in a repetitive manner?
Say if col A is my 1st col... The macro will first delete cols B, C & D, then cols F, G & H, followed by cols J, K & L till end of cols in the worksheet. The macro will delete 3 consecutive cols after the 1st & keeps the 4th col in the count each time it deletes the 3 cols.
It may look something like this:
A B C D E F G H I J K L M N O P...
Delete B, C, D
Keep E
Delete F, G, H
Keep I
Delete J, K, L
Keep M
Delete N, O, P
Repeat the steps till end of columns
Thanks in advance.
Cheers,
Nadia
Hi all!
I desperately need some help from you all.
I download a massive volume of data from some data service into excel before I could use them for analysis each day. I can't customise the download format of data. I have been manually deleting the unwanted columns each day & it's getting a little too much.
Can anyone please help me with the following exercise:
The downloaded data are in the following format:
Date prod1 cons1 cons2 sales1 prod2 cons1 cons2 sales2 ....
The data I need are the sales figures for each unit for each day.
I want to delete all 3 columns before each sales data. How do I write a macro to do that?
Your help is truly appreciated.
Thanks in advance.
Cheers,
Nadia