asked on

Help trying to transfer specific cells from one sheet to next empty row multiple sheets

Hi There,

I'm trying to setup a Command Button Macro to allow me to transfer cell values that are currently on one sheet into various different sheets, and if possible also another workbook.

The information will all be completed on Sheet2, but then when clicking the button, what I want to achieve is:
Cells B9:C9, B10:C10, F14:F17, C14:C15, B11, C17:C18, all copied in that order to the next empty row on Sheet6

Cells A37 and C37, copied into columns E and G of the next empty row on Sheet5

and lastly copy cells B2:B3 and A27:C27 into the next empty row of a seperate worksheet named Worksheet2

I've looked online at various solutions and keep getting a subscript out of range error just when trying to copy from one sheet to another.

Is what i'm trying to do even possible, as it would save so much time if I could do this?

Any assistance or guidance anyone can provide would be greatly appreciated.

Thank You.

Roy Cox

Do you want B9:C9, B10:C10, F14:F17, C14:C15, B11, C17:C18 putting into one row? Currently the ranges are multiple rows.

An example workbook would help

Michael Riddoch

ASKER

Hi Roy,

Thanks for getting back to me.

Yeah I'm trying to get them in the same row for each run of the macro.

The purpose of this is monthly reviews I do with my staff, each person has their own spreadsheet so they can monitor their own progress, but we discuss everything on a single page but then I'd like the macro to filter the data through to the seperate tracker sheets within the same workbook.

I've attached the example workbook and added into the cells where the data should come from/go to
Example.xlsm

Roy Cox

I should be able to write code for you, but I would suggest a better way. Store and enter the data in one Table then us formulas to populate the various other sheets based on the employees ID.

Also, some of your cells in the forms are merged cells, this is not a good idea

Roy Cox

I've put some code as an example for you.

You have a UserForm already for adding data,
Example.xlsm

Michael Riddoch

ASKER

Thanks so much for your help, I've managed to get everything into the relevant sheets now.

The only part I'm still having an issue with is transferring data into a separate workbook to collate.

I have the same table setup on each persons review form and then a separate master workbook and I'd like the new data to also be copied into the table on the master.

Can this be done in a similar way to trasnfer to a different workbook?

Roy Cox

Yes, you need to open the workbook and specify where to copy to. Where is the other workbook stored? I'll check back later and write the code that you need. If you want to attach your workbook I'll check the code that you have written.

Michael Riddoch

ASKER

All of the review forms are in the same folder and the master workbook that i want to collate them to is in the parent folder.

Roy Cox

OK, as soon as I get a minute I'll give you some sample code

Michael Riddoch

ASKER

Hi there,

Thanks for all your help so far, i was going crazy trying things before.

So the last piece that im trying to update is to send the content of cells a21:c21 to a different workbook to be collated. The destination workbook only has the first row containing headers in columns a:c and the new data should add to the next empty row.

The destination workbook is in the parent folder from the source workbook.

Thanks

Roy Cox

Which file will hold the code?

Michael Riddoch

ASKER

The code would be on the source workbook and i'm hoping it would work like the Copy Data button previously used, with the difference being that instead of pasting the data to the next available row of a different sheet in the same workbook, It would paste into the master workbook.

For Example:

c:\Root Folder\Folder\SourceWorkbook.xlsm

When finished filling this one in, clicking the button and cells a21:c21 will be transferred to the next available row of the spreadsheet in the following location.

c:\Root Folder\Folder\DestinationWorkbook.xlsm

Is this doable?

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

Did this work? Some feedback would be appreciated

Roy Cox

No response from the author. The code that I provided works for me.