Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA-Copy and paste data from one workbook to open a new work

0

Hi All,

I want to add new workbook with paste specfic column header data.

Destination: Order date, Region, Item & Unit cost

Source file: Order date, Region, Rep, Item, Units & Unit cost

Answer
Discuss

Discussion

Hi Jagadeesan and welcome to the Forum.   Please edit your original question and use the Add Files... button to upload representative Source and Destination Excel files (without any personal data) to show your existing data (and any macros). Then we should be able to give specific help.
John_Ru (rep: 6042) Mar 7, '23 at 6:21 am
Hello Jagadeesan,
As John asks, a representive file will be very helpful to us so we can help you. Also, what exactly do you want to copy? Just the headers? All of the data in the Order date, Region, Item & Unit cost columns? Only the last entry in each of those columns? The less guessing we need to do will make providing you with a good solution easier; and a solution that is more specific to your situation.
Cheers   :-)
WillieD24 (rep: 477) Mar 7, '23 at 12:34 pm
@Willie Good additional info requests, thanks. This has the feel of a cross-forum question to me though.
John_Ru (rep: 6042) Mar 7, '23 at 1:24 pm
Hi John, I have uploaded source and destination(New workbook) file.
Jagadeesan Mar 9, '23 at 1:48 am
Jagadeesan. Please check your question - you did not upload any files (note that only Excel files are allowed). Also kindly answer the points raised by Willie. 
John_Ru (rep: 6042) Mar 9, '23 at 5:45 am
Sorry for the confusion. I have uploaded 2 excel files
I need all the data under Orderdate,Region, Item & Unit cost
Jagadeesan Mar 9, '23 at 8:27 am
Hi Jagadeesan,
Your sample files clearly show what you have described in your original post. I now have some more questions I would like answered before I spend any time working on a solution.
1) Does the destination workbook already exist or do you want the copy/paste action to create it?
2) If the copy/paste action will be creating a new workbook, what will it be called and where will it be saved?
3) If the destinationI workbook already exists, do you want the copy/paste action to replace the existing data or to add the new data below the exsisting?
4) How often will you need to perform the copy/paste?
5) Would it be OK to add a "Copy" button to the source workbook? (clicking this button would trigger the copy/paste action)

If there is anything else you can think of that would be helpful please provide that information/details as well.
The answers to these questions will affect how much time and effort will be needed to provide you with a solution. We much prefer to provide a solution once rather than provide a solution and then have to rework it 2,3,4 times to meet the needs of the poster.

Cheers   :-)
WillieD24 (rep: 477) Mar 9, '23 at 10:37 am
Add to Discussion

Answers

1

Hi Jagadeesan,

Even though you haven't provided the additional details asked for, I played around with this just for fun. (there are elements in the code which I will find helpful with future projects) The macro code as written only works on my machine, so you will need to edit the code to match your situation. (file is attached)

I came up with 4 different methods:

1) copy the existing file and then delete the unwanted columns

2) create a new workbook and copy the desired data to the new workbook

3) copy the desired data to an existing workbook and overwrite the existing data

4) copy the desired data to an existing workbook and add it below the existing data

If you (or someone you know) is familiar with VBA, modifying the coode should be easy.

If you find this solves your dilemma please mark my answer as selected.

Cheers  :-)

Discuss

Discussion

Well done Willie, that's a comprehensive answer for a seemingly unresponsive user. 
John_Ru (rep: 6042) Mar 13, '23 at 6:14 pm
@ John_Ru:
Thanks John. Since Jagadeesan never responded with the additional details, I decided (for my own practice) to work on some possibilities. If more information had been provided, my solutions could have been more specific. The code only works on my machine because each method references an existing file on my machine. I have tweaked some of the code to address a couple of (cosmetic) formating issues. (revised file attached to my answer)
If Jagadeesan ever does respond with more details, then I can work on modifying the code to his situation.

Cheers   :-)
WillieD24 (rep: 477) Mar 14, '23 at 12:12 pm
@Willie Well done again. I suspect Jagadeesan will remain wilent (sadly, given your efforts). 
John_Ru (rep: 6042) Mar 14, '23 at 12:45 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login