Excel question: i have to think there's a way to do this

[Spooner]

Is there a way to gather information that is inputted on a worksheet, and paste that into another worksheet via a macro? In other words, if there's a bunch of data on one worksheet for that given week, but i want to press a button whereby that data is copied into another worksheet to maintain historical data of each week. I was easily able to record a macro to do the copy/paste, but i can't figure out how to automatically append a new row each time the button is pressed

help is appreciate

 

[BrokenVisage]

Yeah that'd be pretty easy to do. If I have free-time later i might be able to whip it up, but it's something that you could find all over the net using Google if you look. OZGrid, MrExcel, BigResource.. these places are all about macros in Excel.

 

[Tweak155]

Is there a way to gather information that is inputted on a worksheet, and paste that into another worksheet via a macro? In other words, if there's a bunch of data on one worksheet for that given week, but i want to press a button whereby that data is copied into another worksheet to maintain historical data of each week. I was easily able to record a macro to do the copy/paste, but i can't figure out how to automatically append a new row each time the button is pressed

help is appreciate

Append a new row? Are you just creating a new tab each time? Or appending the data below previously existing data?

Not being specific enough, but try playing with this:

Dim rngDestination as Range

With ThisWorkbook.Sheets("CopyDestinationSheet")
    Set rngDestination = .Range("A" & .Cells(65535, "A").End(xlUp).Row + 1)
End With

'rngDestination now has the cell you should paste to... do the paste

Change .Cells(65535, "A")... change the "A" to the column that will have the last data written to it.

 

[Spooner]

There's only two worksheets. 1 where you enter data for the current week, and 1 that has a running historical total for all weeks YTD. I'd like to have a macro whereby a push of a button will update the YTD sheet automatically instead of having to manually copy/paste each week.

Hope that helps.

Can i record this easily from the front end, or will i have to manipulate the code like posted above?

 

[Tweak155]

There's only two worksheets. 1 where you enter data for the current week, and 1 that has a running historical total for all weeks YTD. I'd like to have a macro whereby a push of a button will update the YTD sheet automatically instead of having to manually copy/paste each week.

Hope that helps.

Can i record this easily from the front end, or will i have to manipulate the code like posted above?

You will have to manipulate it because Excel can't extrapolate that you're picking the next blank row because its the next available. It just knows you're picking a cell.

 

[DrPizza]

You will have to manipulate it because Excel can't extrapolate that you're picking the next blank row because its the next available. It just knows you're picking a cell.

Not really, because the macro can keep track of which cell is the next blank cell (or can even look for it.)

 

[Tweak155]

Not really, because the macro can keep track of which cell is the next blank cell (or can even look for it.)

I don't think you even know what you're saying, or you're not following what the thread is saying.

 

[Spooner]

so, Tweak, in your approach... do i just record the copying/pasting of what cells go into which cells, then go back and add a bit of code to tell it to add a new row each time?

sorry for being a bit simplistic, i'm certainly no coder

 

[Tweak155]

so, Tweak, in your approach... do i just record the copying/pasting of what cells go into which cells, then go back and add a bit of code to tell it to add a new row each time?

sorry for being a bit simplistic, i'm certainly no coder

So when you record the macro and you go to the sheet to paste the data, let's assume its a blank sheet.

Are you picking "A1" and hitting paste or are you doing something else?

EDIT:

Better yet, just paste the recorded macro here for ONE set of data.

 

[Spooner]

I'm copying from the selected cells in worksheet 1 and pasting the values in the appropriate cells in worksheet 2

this all works splendidly until i run the macro again and it overwrites the same row of data, instead of adding a new row

 

[Tweak155]

I'm copying from the selected cells in worksheet 1 and pasting the values in the appropriate cells in worksheet 2

this all works splendidly until i run the macro again and it overwrites the same row of data, instead of adding a new row

Unfortunately it is very hard to answer your question. You need to be more specific. I think the macro would clear up these things.

 

[Spooner]

OK - i'll need a couple hours then i will post. Appreciate the help

 

[Tweak155]

OK - i'll need a couple hours then i will post. Appreciate the help

No problem... unfortunately this is my 9-5 (VBA in Excel / Access, although I get a little C# / Java exposure). No one wastes their time learning it so I'm "in demand"... at least for now.

I can tell you that at minimum you will need to manipulate the macro, but how much depends on what you're doing. If its a row at a time, that's easy. If its specific cells and then you want to line them up, that's a little more time.

Overall though, it shouldn't take a lot of manipulation... just want to make sure I get it right

 

[sdifox]

It's easy. Just create a helper cell somewhere, like in sheet 3 where you keep track of what the current first empty row number is. then use that information to decide where to paste.

 

[Tweak155]

It's easy. Just create a helper cell somewhere, like in sheet 3 where you keep track of what the current first empty row number is. then use that information to decide where to paste.

This is the part you can't do by simply recording a macro.

 

[NoTine42]

When recording the macro, can't you go into the new worksheet, hit [cntl]+[end] then arrow to the next open cell?

 

[Tweak155]

When recording the macro, can't you go into the new worksheet, hit [cntl]+[end] then arrow to the next open cell?

Yes but its not always accurate.

Try this -

Open a blank excel sheet, put some data in and make sure there is a gap. If you hit ctrl end, it will highlight the outer boundary of whatever you filled in.

Now clear all the cells contents.

Select A1 or somewhere in the first row and hit CTRL END again. It will take you back to that same cell, even though there is no data on the page.

In other words, that method is not reliable.

 

[NoTine42]

Yes but its not always accurate.

Try this -

Open a blank excel sheet, put some data in and make sure there is a gap. If you hit ctrl end, it will highlight the outer boundary of whatever you filled in.

Now clear all the cells contents.

Select A1 or somewhere in the first row and hit CTRL END again. It will take you back to that same cell, even though there is no data on the page.

In other words, that method is not reliable.

It's not ideal, if the OP ever deletes data from the historical workbook, but I believe the biggest risk is extra, blank cells, and not overwritten data.

I know there are better solutions possible..I'm wondering why not store the data in a database, at least Access where importing data is a basic function.

 

[sdifox]

This is the part you can't do by simply recording a macro.

op said marcro, not record a macro. Besides, you can record a macro then edit it.

 

[Tweak155]

Is there a way to gather information that is inputted on a worksheet, and paste that into another worksheet via a macro? In other words, if there's a bunch of data on one worksheet for that given week, but i want to press a button whereby that data is copied into another worksheet to maintain historical data of each week. I was easily able to record a macro to do the copy/paste, but i can't figure out how to automatically append a new row each time the button is pressed

help is appreciate

There's only two worksheets. 1 where you enter data for the current week, and 1 that has a running historical total for all weeks YTD. I'd like to have a macro whereby a push of a button will update the YTD sheet automatically instead of having to manually copy/paste each week.

Hope that helps.

Can i record this easily from the front end, or will i have to manipulate the code like posted above?

op said marcro, not record a macro. Besides, you can record a macro then edit it.

/facepalm

Try reading the thread. We are getting to the edit part.

 

[Tweak155]

It's not ideal, if the OP ever deletes data from the historical workbook, but I believe the biggest risk is extra, blank cells, and not overwritten data.

I know there are better solutions possible..I'm wondering why not store the data in a database, at least Access where importing data is a basic function.

Likely he receives the data in Excel or it is already in excel by some fashion.

A worksheet is not unlike a table. In fact, you can set up a worksheet AS a table in code and query data from it.

 

[sdifox]

/facepalm

Try reading the thread. We are getting to the edit part.

OP got his answer that it cannot be automatically done though record macro... However, he can record the macro, and just edit the copy reference address and change it into a formula.

 

[DrPizza]

No problem... unfortunately this is my 9-5 (VBA in Excel / Access, although I get a little C# / Java exposure). No one wastes their time learning it so I'm "in demand"... at least for now.

I can tell you that at minimum you will need to manipulate the macro, but how much depends on what you're doing. If its a row at a time, that's easy. If its specific cells and then you want to line them up, that's a little more time.

Overall though, it shouldn't take a lot of manipulation... just want to make sure I get it right

Oh, I see what you mean now by manipulate the macro - I haven't needed to use scripts in a while - I used to simply code it, rather than record it.

 

[sweenish]

Recording is only really useful if you don't know how to do anything/one particular thing. There's so much slop and extra crap in recorded macros that they're not worth it.

Write the macro yourself, or get advice from the sites listed above.

This won't take long for someone to provide a full solution.

The steps are going to be simple:
Open destination workbook
Scan destination workbook for next empty cell
Copy data
Paste data
Save destination workbook [and close it]

The copy data step can be moved as needed, and making the macro dummy-proof will take a lot more work (e.g., if the destination workbook is moved then fall back to a locate file dialog, enforce proper formatting).

But all in all, this is a relatively easy solution.

 

[sweenish]

It's not ideal, if the OP ever deletes data from the historical workbook, but I believe the biggest risk is extra, blank cells, and not overwritten data.

I know there are better solutions possible..I'm wondering why not store the data in a database, at least Access where importing data is a basic function.

I would hope Access is a part of the big picture at some point, but YTD info in a spreadsheet makes sense as well.