Pull column from one worksheet into another

Question

Hello!

I am trying to pull data from one workbook into another, but I am having some serious issues.

The data column I need to pull from is AD (technically the data begins in cell AD14), but when I tried writing a VBA code it said "cannot copy data from merged columns". I cannot move the data into an A-Z column because there is already data there.

I also tried writing a formula in the cells (not VBA) but then I could not sort/filter the data the way I wanted to, so that was not helpful.

I would like this to be dynamic so when new data is entered into the original workbook, it copies into my new one. Is this possible?? There are many worksheets in the original workbook that I would like to pull columns from, but I am a beginner in VBA so a lot of the coding I have tried to look up is too confusing to understand how to manipulate it.

Could someone please write a mock code using basic "Workbook1, Sheet1" type names so I know where to change to the specific workbook/sheet names that I need?

Please help, if possible, I am desperately trying to figure this out but am having no luck whatsoever :-(

Answer 1

OssieMac,

I waited a few minutes after posting my email and now have just deleted it. Please let me know if I did not wait long enough and I will try again! :)

Answer 2

I can't access the file. If you want to post your email address then I will reply. However, you cannot simply post the email address as such because the system automatically removes email addresses from posts. You need to post as follows:

name at domainname dot com

If your email also has a country etc then add another "dot" and country code. You can leave the post up for 5 minutes or so to give the system time to send the email notification to me and then if you like you can come back to it and edit and delete it.

After I reply you can reply back to me with the example workbook as an attachment.

Answer 3

Hi OssieMac,

Thank you SO MUCH for taking the time to reply and see if you can help me out!

I hope this file is enough (and I hope you can edit it, I've never used OneDrive like this so I'm not sure if I did it right) but here is the link to the zipped folder: https://bmrn-my.sharepoint.com/:u:/r/personal/sy898661\_bmrn\_com/Documents/Example%20Workbook.zip?csf=1&e=WXnwoX

There are a lot of permission rules in my company and on the network we use, so if you are unable to view this, please send me your email and I will give you permission.

Unfortunately all of the information on my workbook is completely confidential, including things like column titles/other random information so there is not very much info on the example workbook in the file. I just put in where the column is located and entered some example dates in the correct format so you can somewhat see what I'm working with. If you are unable to help with what I have provided, I completely understand.

In the column I would like to copy over, there are some lines with formulas but most of the data is manually entered in. I just need the numbers (dates), no formulas whatsoever.

I tried writing this code in the VBA editor for each of the worksheets I wish to copy from (I only included one of them), and it kinddd of works but also does not really return all of the data I want. Plus, if the data ever passes cell AD100 that info will not be returned.

Workbooks("NOVMS").Activate <br><br>Workbooks("NOVMS").Sheets("ASB").Activate <br><br>Range("AD14:AD100").Select <br><br>Selection.Copy<br><br><br><br><br><br>Workbooks("S&OE").Activate <br><br>Workbooks("S&OE").Sheets("Sheet1").Activate <br><br>Range("A2").Select <br><br>ActiveCell.PasteSpecial <br><br>Application.CutCopyMode = False <br><br>ActiveCell.Offset(0, 1).Activate

Thank you again so much!

Sydney

Answer 4

Need some more information.

How are the merged cells set up on the worksheet? ie. are the merged cells all across the same columns or are they one under the other in rows? I need to see how the merged cells relate to the unmerged cells in relation to offset etc to determine the best way to achieve the end result.

I also need to know if we are copying formulas or values and if formats are to be copied also.

If you can upload an example workbook to OneDrive with example data in the range to be copied and then tell me the destination then I will see what I can do. If your workbook contains sensitive data then I do not need all of it. Just create a dummy workbook with some example data and example merged cells within the data range. It will help if you can use the Fill to highlight the merged cells.

Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
4. Go to this link.  https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file on OneDrive and select Share.
9. Select "Get a Link" from the popup menu.
10. Click in the field displaying the link and Ctrl and A should highlight the entire link and then Copy and Paste the link into your reply on this forum. (I suggest that you avoid the "Copy" button on the "Get a link" screen because it introduces additional steps that are not required.)