Hi Guys,
I need help to copy a range in multiple worksheets in different workbooks and paste them into different worksheets in the active workbook.
I have code that will copy a range in a worksheet and paste it into another worksheet in a different workbook, this all works well but I really need some help on how I could modify it to copy multiple worksheets.
Hope someone can help me?
Please Login or Register to view this content.
You need a looping structure.
The loop can open each of multiple predetermined workbooks,
Then loop through multiple sheets in that current open workbook and copy the data to the destination workbook.
In order to assist we need more info about where the multiple source workbooks reside (in a folder)
What sheets contain the info you need copied and if it can be the entire sheet or just a specific range, and if a range, is it the same range each time, or could it be different sizes of ranges.
IT would be great if you can upload a sample file that illustrates the source data to copy. Please ensure it is desensitized.
Regards,
Rudi
Hi Rudi,
Thank you so much for replying!
The multiple Source books will reside in the following folder C:\Users\HQ\Downloads and the Destination Workbook will reside C:\Users\HQ\Documents\Data.
Each source Workbook contains just 1 Worksheet and the ranges differ from sheet to sheet but you could just copy the entire column or row depending what info is on that worksheet. The Destination workbook would normally be open and the Source workbooks closed. In the Destination workbook there will be worksheets corresponding to the worksheets to be copied in the Source workbooks.
I have attached 3 Sample Source workbooks and the Destination workbook
Sample Source Workbooks
sales-by-hour-42233.csv
sales-by-month-71888.csv
sales-by-period-78904.csv
Destination Workbook
Final Destination.xlsm
Hope the above helps?
Regards
Derek
Thanks for the concise and clear reply.
I'll see that I can set up for you...
Hi,
Give this Sales Import Template a run and see it it does what you need...
Hi Rudi,
I tested out and looks very impressive but there is a couple of things I didn't explain properly to you. So Sorry!
I don't need to remove any of the worksheets in the Destination folder because I have other data and formulas on all the worksheets in the Destination workbook that I need to keep.
I need to copy the exact Columns or Rows "Respectively" on the worksheet in the respective Source workbook and then paste it into the respective worksheet in the Destination workbook with the same Range eg: Col "A:Z" or Row "1:8" etc.
So for example the Source Workbook "sales-by-hour-42233.csv" has a worksheet called "sales-by-hour-42233". I need to copy the column range "A:Z" and then paste it into a worksheet in the Final Destination.xlsm workbook called "sales by hour" who's range would be "A:Z" the same as the source worksheet.
were as ................
for example the Source Workbook "sales-by-month-71888.csv" has a worksheet called "sales-by-month-71888.csv". I need to copy the Row range "1:8" and then paste it into a worksheet in the Final Destination.xlsm workbook called "sales by month" who's range would be "1:8" the same as the source worksheet.
Instead of picking a Source workbook every time is it possible to copy all of the Source workbooks respectively and paste them into their respective worksheets in the Destination Workbook using their respective Source worksheet ranges? All of the Source workbooks are always going to be in the Download folder so I would be picking them all!
Hope I haven't confused you more
regards
Derek
Hi,
Here is the updated macro.
I have scrapped the button meaning that you will need to run the macro from the VIEW-MACROS ribbon button or by pressing ALT+F8 and then run the macro called: GetSalesCSVData
If you need the macro to run in another file, just copy it from this workbook to that file...
Run it and see if it is doing what you need...
Rudi..... U dah man!..... exactly what I wanted to the T!
Thank you so so much, this will save me allot of time coping & pasting etc.
Can I be brazen and ask if you could explain the logic to the code? I can figure out some of it but other parts are beyond me!
Cheers....
Hi,
Good to hear it is working, and glad I could help.
Regarding the code...
This sets the base location and stores it in the Folder variable.
sFile is another variable that stores the first file in the sFolder location
Dir() is a VB function that collects files in a folder (in this case specifically CSV files based on the *.csv extention)
ScreenUpdating switches off the screen refresh...speeding up the macro and preventing screen movementPlease Login or Register to view this content.
The Do loop makes use of the VB function InStr() to inspect the file name loaded into the sFile variable by Dir.
The syntax is: InStr(Phrase to search, Term to search for within the Phrase)
The InStr(...)>1 (ideally could be >0; I changed it below, though it will not make a difference in your macro) means that if it finds the term in the phrase, the returning value of InStr = the start position of the term. Basically, if it finds the term in the phrase it will be >0 (eg: =InStr("Hello big world", "big") will equal 7)
It determines if the filename has any of three key words (hour, period and month). It needs this to know which sheet it is processing and to which destination sheet it needs to paste the data. Having said this...it is important that these terms are present in the file name and the sheet names.
Once the
Here the code opens the referenced workbook from the file pathPlease Login or Register to view this content.
Stores the file name of the opened workbook
Copies the current region range from the source to cell A1 in the destination sheet
Closes the source file
CurrentRegion is a special selection property that extends a selected cell outwards until it encompasses its entire block.
Lastly, Dir() is called again to populate the sFile variable with the next File name in the folder.
The Do...Loop will execute until the last file in the folder (that is CSV in this case...based on the filter *.csv)
This finished the macro by switching screen refresh mode back on.Please Login or Register to view this content.
Hope that explains a bit more about the code...Please Login or Register to view this content.
Last edited by RudiS; 05-03-2014 at 03:51 PM.
Hi Rudi,
Sorry for the late reply!
Thanks for explaining the code to me, makes allot more sense to me now!
Again thank you for all your help!
regards
Derek
Hi Rudi,
So Sorry to bother you again but I forgot to ask is it possible to not only to look for ".csv" files in the code but also other file types like ".xls" & "xlsm"???
regards
Derek
Hi,
The Dir() function can only take 1 extension argument. If you need to process CSV's as well as XLS* combinations you will need to use:
sFile = Dir(sFolder & "\*.*") - which will process any and all files in the folder and test each one for the extension before further processing it.
Of course, if it is ONLY excel files, you can use:
sFile = Dir(sFolder & "\*.xls*")
Let me know what is required....
Hi Rudi,
It would be mostly be .csv & .xls files
regards
D
Hi Derek,
Replace your current code with this new code (below). It checks for the two file types to process.
Please note that the *.xls type files must have the data on sheet 1 of each of these files. A CSV file usually only has one sheet so I have made no reservations in the code to check for sheet counts, etc. If the data you plan to transfer is not on sheet 1 (the first sheet) in the *.xls files, the data will not come through to the destination properly.
Cheers!
Please Login or Register to view this content.
Hi Rudi,
I tested the code but it comes up with an error "Subscript out of range" the following line in the code is highlighted
"ActiveSheet.Range("A1").CurrentRegion.Copy ThisWorkbook.Sheets(sName).Range("A1")"
I added two extra sheets onto the code but I think the problem lies with the "Expenses by Contact.xls" Workbook & worksheet? On this sheet the 1st row is Merged
from cells A1 to F1 and the three subsequent rows after that as well. I think this is why we are getting the error??
I have attached the files so you know what I mean.
D
Hi,
This code will work...
The problem was that the Expenses sheets data did not start in Cell A1 (as the csv files usually do).
I compensated for this by changing the range property called CurrentRegion with a Sheet property called UsedRange, which has a wider scope in getting the block of data to copy.
Test if this code runs OK...
Please Login or Register to view this content.
Last edited by RudiS; 05-04-2014 at 03:37 PM.
Hi Rudi,
I tested it, but even though it says "importing Sheets Complete" nothings importing at all?
Strange??
D
Oops...sorry my fault.
I forgot to remove my path for testing.
I edited the macro above and removed my test path and replace it with your path again: sFolder = "C:\Users\HQ\Downloads"
If you copy the macro above again and run...it should work fine now!
Hi Rudi,
I found the problem the sFolder = "C:\Users\rudis\Downloads" ..... should be ....... sFolder = "C:\Users\HQ\Downloads"
Where it says "rudis" it should be "HQ" for me. Other than that it worked like a charm!
Again what can I say? U Dah Man! Thanks so much for going beyond the call of duty to get this right for me!
regards
D
It was a pleasure to help...
See you around...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks