• 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.

VBA Cope range from one workbook and paste in another workbook

H

hanim554

Member
Dear Friends,

I would like to have a code to do the following, could any one please help me.

I have excel work book opened, when I run code I want to copy range C5:M500 from sheet “ Extract” and update in the same range in the File named Report in the sheet “ Data” which existing file in C. This work book remains closed when I run the code, also I need update this work book name with adding current date. For example, if I am running code today, file name in the C drive will be Report-23-10-2013.

Appreciate any help.

Regards
Hanim
 
I'm afraid I got a little confused there. Where is the file? Are we copying data to somewhere? If so, there's no way to do that w/o opening the file. Can you post an example of what your data looks like?
 
If I am getting is right, you want to copy range C5:M500 from activeworkbook and then openup the report file stored in C drive and paste the copied data in the Data sheet at cell C5:M500 and then rename the Report file as Report followed with today's date..

Is this correct?
 
Hi, hanim554!

This is the n+1(th) variation of the same widely posted question: to get (read, retrieve, find...) or to put (write, set, ....) anything that's within a file (which is within a folder, and so on...) the operative system (usually Windows but valid for any other) should open the file.

No matter how it's done, if directly from the OS, if by a running instance of an application (Excel), if by code within a workbook (yet opened from the Excel application), ... someone somewhere somehow should have to open the file in order to do what required.

Regards!
 
Dear All,
Thank you for your reply, i just reached from office, sorry i couldnt have an early reply.
Dear Abhijeet, your word is correct exactly what i am looking for, could you please help me.
Thanks
Hanim
 
Abhijeet R. Joshi said:
If I am getting is right, you want to copy range C5:M500 from activeworkbook and then openup the report file stored in C drive and paste the copied data in the Data sheet at cell C5:M500 and then rename the Report file as Report followed with today's date..

Is this correct?
Click to expand...

Dear Abhijeet,

Correct, this is exactly what i am looking for, could you please help me.

Thanks
Hanim
 
Hi Hanim,

Here is the code for you.

Code: 
Sub copy2report()
wb = ActiveWorkbook.Name
Workbooks.Open "C:\Report.xls" 'change path as required
Windows(wb).Activate
Range("C5:M500").Copy 'define the range to copy
Windows("Report").Activate
Sheets("Data").Select
Range("C1").PasteSpecial xlPasteAll
Application.CutCopyMode = False
ActiveWorkbook.SaveAs "C:\Report" & Format(Now(), "DD-MM-YYYY") 'change date format as required
End Sub

Hope this helps..:)
 
Dear Abhi,
Thank you so much. it is great help for me....
If I want to create new file and paste the same instead of pasting existing one, which part of Code i would change.
Regards
Hanim
 
Code: 
Sub copy2report()
'Workbooks.Open "C:\Report.xls" 'change path as required
Range("C5:M500").Copy 'define the range to copy
'Windows("Report").Activate
'Sheets("Data").Select
Workbooks.Add
Range("C1").PasteSpecial xlPasteAll
Application.CutCopyMode = False
ActiveWorkbook.SaveAs "C:\Report" & Format(Now(), "DD-MM-YYYY") 'change date format as required
End Sub
 
Hi All
I want to copy value from one workbook and paste it in another workbook using paste special could anyone suggest VBA code for this

Eg: I want to copy (given in rows)
99
132
And paste it in different sheet in the format (in column) 99 132
 
@Deepesh
Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Regards!

PS: Please don't answer here at this thread.
 
hi Abhijeet,
I have a problem, I want to update my 'characterization list' workbook automatically. Below the way my workflow to be;
1.Open main template workbook(name as 'summary') and I fill up a few row of data.
2.I want to insert the updated data from above mentioned workbook(summary) into another workbook(name as 'characterization list') by clicking one button inside the 'summary' workbook.
3.by clicking the button I can open the 'characterization list' workbook and update the new data info automatically in a new row.
please help me to find the solution. I have tried many times but still no solution :-(
 
I missing something to mention earlier. workbook 'characterization list' has many worksheets. I only want to insert the updated data into one respective sheet only(i.e worksheet 'info')
 
You must log in or register to reply here.
Back
Top