Copy and paste between workbooks

1
votes

This is a question and may or may not be achievable but any recommendation is much appreciated.

Is it possible to have one workbook (wrkbookA) open that has all the data and vba code in it. Click button to open/navigate to a directory and select another workbook (wrkbookB). wrkbookB is opened, select a column, that column is copied and pasted into wrkbookA in column A and wrkbookB is closed.

I know this can be achieved using a constant column from wrkbookB but the data is in differnet columns in each workbook in wrkbookB.

I have also looked at column headings and doing the copy that way but it is not an option as there are different columns with similar heading/words.

Thanks everyone.

1
vbaexcel
If you can't determine which column the data should go in, then you're going to have to ask the user, maybe using an inputbox? – stuartd
Stuart, The column that needs to be copied is not known, even if i do use ask the user, what would the code look like? Thanks. – Fwafa
I recommend taking a step back and see if you can standardize wrkbookB first. Why would the data be in different locations every time? If someone is entering data by hand, you can just give them a heavily protected Excel template. – PowerUser
That is the problem. The data is within these workbooks and each workbook comes from different companies. I cant ask the companies to standardise their workflow for me :) Is what described above possible? If not any suggestions please? Thank you. – Fwafa

1 Answers

1
votes

Since you don't have any way of knowing which Column will need to be selected from workbook B, you're going to need user interaction. that being the case, why not write a sub that will do the following:

  1. Have user select workbook B to open
  2. Place a button in Workbook B attached to code in Workbook A

User then, in Workbook B, selects the column they want copied and push the button which copies that column to Workbook A and closes Workbook B without saving changes.

Sample Code Idea:

Workbooks.Open ...
ActiveSheet.Buttons.Add(427.5, 12.75, 104.25, 36).OnAction = "WorkBookA!MoveColumn"