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.
Michael RiddochAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
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 RiddochAuthor Commented:
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
Roy CoxGroup Finance ManagerCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Roy CoxGroup Finance ManagerCommented:
I've put some code as an example for you.

You have a UserForm already for adding data,
Michael RiddochAuthor Commented:
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 CoxGroup Finance ManagerCommented:
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 RiddochAuthor Commented:
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 CoxGroup Finance ManagerCommented:
OK, as soon as I get a minute I'll give you some sample code
Michael RiddochAuthor Commented:
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.

Roy CoxGroup Finance ManagerCommented:
Which file will hold the code?
Michael RiddochAuthor Commented:
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?
Roy CoxGroup Finance ManagerCommented:
Hi Michael

Try something like this
Dim oWb As Workbook
    ''/// open destination workbook
    Set oWb = Workbooks.Open("c:\Root Folder\Folder\DestinationWorkbook.xlsm")
    ''/// hide actions from user
    Application.ScreenUpdating = False
    ''/// error handler to restore screenupdating if error occurs
    On Error GoTo exit_copy
    ''/// copy data to other workbook(amend sheets as required
    With oWb.Sheet1
        Sheet1.Range("a21:c21").Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
    End With
    ''///close and save
    oWb.Close True
    ''///restore screenupdating
    Application.ScreenUpdating = False

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Did this work? Some feedback would be appreciated
Roy CoxGroup Finance ManagerCommented:
No response from the author. The code that I provided works for me.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.