• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to copy data from one workbook to another

vrunda

vrunda

Member
I have one workbook for 1 customer with all info. I want to copy the details from fixed range of cells at different locations, to another workbook. This i want to do for 100 customers. I have tried recording macro but it is recording name of workbook , so in genearl i cannot use same macro for other customers. Macro is below:

Code: 
Sub Copydata()
  Windows("adgulhane1FORM-704v2  .xls").Activate
    ActiveWindow.Activate
    Range("C7:J7").Select
    Selection.Copy
  
    Windows("1FORMAT2014-704v2.xls").Activate
    ActiveSheet.Paste
     ActiveWindow.Activate
    Windows("adgulhane1FORM-704v2  .xls").Activate
    Range("H8:J8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("1FORMAT2014-704v2.xls").Activate
    Range("H8:J8").Select
    ActiveSheet.Paste
End Sub

This is for trial hence only two range are selected in first workbook.
There are many different ranges to copy from first to second workbook.
The files name in red color will change for different customers.
In 1st & 2nd workbook copy paste ranges are same & sheets name are also same, only workbook name will differ.
 
Last edited by a moderator:
Hi,

You need to create variable to store all the workbook names and put the variable in loop and for the source workbook you can "Thisworkbook" function.

Regards
Abdul Matheen
 
Pls can u give whole coding for this.I can open both the source and destination workbook at a time to copy the data. I can create a button on QAT and run the macro each time but the source workbook name changes. And moreover the foloowing is also not working...I mean only first copy paste is happening correctly but further not.
Code: 
Windows("adgulhane1FORM-704v2  .xls").Activate
    Range("H8:J8").Select
    Application.CutCopyMode = False
    Selection.Copy
 
Hi Vrunda ,

Can you upload a workbook with the following data :

1. What is the source workbook name ?

2. What are the locations from which data is to be copied ?

3. What is the destination workbook name ?

4. What are the locations where the copied data is to be pasted ?

Give the above data for 3 customers.

Once the code is written , you can easily extend it to the desired number of customers.

Narayan
 
I cannot upload the workbooks, as their size is 7 Mb each. Both are forms . Source is Previous year form & destination is current year form, Source workbook is in name of customers & destination is FormatFinal.xls
Data is to be copied from sheet "PartI" Row7,8,9,10
& sheet "Part II" Row 7 to 60.
 
Hi Vrunda ,

It is not clear why you are talking of forms , when the copying is to be done from worksheet cells to worksheet cells.

For the sake of this question , you can remove all the unnecessary sheets and then upload the workbooks ; it does not matter if the formulae all show error values ; all that matters is the data layout and the tab names.

Narayan
 
By forms I mean it is statutory form in excel sheets. Finalformat.xls is destination workbook.
 

Attachments

  • FinalFormat.xls
    731 KB · Views: 8
  • HDMFORM-704v2 .xls
    732 KB · Views: 5
The data from source workbook to be copied is as I said above , including partII Row no89 to 100 .
These workbooks are pre-filled & white columns we have to fill(Right side).
 
Hi Vrunda ,

There is still some confusion ; can you clearly spell out the cells to be copied ?

As far as I can see , in your posts , I can find :

Data is to be copied from sheet "PartI" Row7,8,9,10

sheet "Part II" Row 7 to 60

partII Row no89 to 100
Click to expand...

In Part I , I can see data in cells C7 , H8 and H9 ; row 10 does not contain any specific information which needs to be copied from the source.

In Part II , I can see data in the rows between 61 and 88 , with some checkboxes in between being checked ; what about all this information ?

You need to be very specific about cells rather than rows ; since this is a template , it is better to transfer data from one cell to another , instead of using copy + paste ; copy + paste does not work well with merged cells.

Narayan
 
Yes Sir, U r correct row 10 in PartI does not need copy of data . In partII I need copy from B81:K86 & not 62 to 81 does not need to be copied as for every year it might change.
Then for PartII cell B89:K98 , G7:k7, G8:H8, g9:K12, B15:K17, H24:k40 , I42:k42, B45:k48 ,C54:k54, B56:I60, j56:k60 , G61:k61 is to be copied.
Part I c7:J7, H8:j8 & H9:J9, if possble B60:J75 needs copy of data
 
Hi Vrunda ,

Let me go through the form , and verify all this. Please check back tomorrow.

How do you want to do it for 100 customers ?

Narayan
 
Same form is used for every customer by filling the customer data. Hanuman Dal Mill (HDM) is one customer. So for every other customer same cells are to be copied from their old source workbooks respectively in blank destination workbook. Every time source workbook name will change.
 
Hi Vrunda ,

I was wondering if it would be better to copy the entire worksheet , and then clear the unwanted data ?

Narayan
 
Will that be possible.? But still the cells will be merged. I shall see if its suitable. You pls send the code so I can work on my WOrkbooks.
 
Hi Vrunda ,

Can you go through the following text file and confirm that these are the only data locations ?

After confirming , please upload a file which will contain only those locations out of those in my file , which need to be cleared i.e. once you re-upload my file , the locations which are present in the file will have their contents cleared. All other locations will retain their contents.

Narayan
 

Attachments

  • Form_Locations_To_Be_Cleared.txt
    761 bytes · Views: 4
Uploading file for locations to be cleared OF partI sheet
 

Attachments

  • FinalForm_Locations_To_Be_Cleared.txt
    490 bytes · Views: 5
For sheet PartII locations to be cleared h81, h82,h83,h84,h85, h86
& J81,J82, J83, J84, J85, J86
& I78
 
Hi Vrunda ,

One more point which I forgot to mention ; in the copied file , all checkboxes will be unchecked ; is this OK ?

Narayan
 
Hi Vrunda ,

Both the workbooks you uploaded have their VBA code password protected.

Where do you want this code to be written ? In a new workbook ?

Narayan
 
Sir , I shall write in personal workbook & place macro on QAT to run it for copying data. Or U suggest
 
Actually what I was thinking that I will open source & destination workbook each time I need ,to copy from different source wbks. & run macro. Ofcourse i have to do this for n no.of customers n times, ,becoz source workbook names r different.
 
You must log in or register to reply here.
Back
Top