VBA Copy insert into another workbook _ excel 2013

Hi

I need some help here with the following. - Using Excel 2013

I have a temp worksheet which I format some data - i will be running the macro on this workbook;

What i need to do is then go to row 2 select all the rows with data in them (i need to select rows as i have data going into column "w" and possibly further)
With that data selected i need to insert the data into another excel workbook on my c drive called "Data".

However this workbook also contains data already in it - so what i need to do is to insert the data into the last row which does not contain the data

Please please can someone help with the coding here will REALLY APPRECIATE IT!!

Please if you do post any code just preface it so i can understand what is happening at each stage - i do understand a bit of vba but that is just through trial and error with the vba editor

Regards

Hi

Couple of lines that may help you below, one finds & selects used range minus row 1 (I assumed by your question that you had headers ?) & the other selects row after last used row of active worksheet.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Regards
Steve

Hi Steve,

Thanks for those steps they do help - but after selecting the used range how do insert those rows in the "data" workbook is there some code you could post for that please ?

Regards

Just working on that (I'm a beginner myself so not as quick as others to answer!), will the workbook "Data" be open or does the macro have to open it, also what is the name of the worksheet that the data should be copied to?

After your copy command if Data is already open

Please Login or Register  to view this content.

Hi

there is only one sheet in that book and yes the macro will have to open it i believe unless it doesnt need to - the name of the worksheet changes so that cannot be used as a reference point

Regards

To open workbook (in this example it is on my desktop)

Please Login or Register  to view this content.

Using John's code line from above reply this may work for you if you change file path for "Data" workbook and worksheet name

Please Login or Register  to view this content.

Hi John

If I rename the sheet name everytime i use the macrowith yesterdays date isnt that going to be a problem in that coding ?

John,

If you dont mind, is the code I enetred ok? it seems to work but wouldnt want to lead someone down the garden path so to speak!

if you only have 1 worksheet in the "Data" file you can use the "ActiveSheet.Name" as below. This way it doesnt matter if you keep renaming worksheet.

Please Login or Register  to view this content.

Forgot to save and close "Data" workbook so added a few lines for that too.

Please Login or Register  to view this content.

Originally Posted by silverxx12

Hi John

If I rename the sheet name everytime i use the macrowith yesterdays date isnt that going to be a problem in that coding ?

Yes as it stands now, but it can be modified depending on how you enter the date

Please Login or Register  to view this content.

Originally Posted by Steve@TRW

John,

If you dont mind, is the code I enetred ok? it seems to work but wouldnt want to lead someone down the garden path so to speak!

Steve, your code seems fine too me. It should work just as well.

Hi, dont know if you need this but added more code to automatically change worksheet name in "Data" workbook to yesterdays date when macro is run.

Please Login or Register  to view this content.

Ok

I am using part of both of your codes as below:

Please Login or Register  to view this content.

The problems i am faced with are as follows :

the header row from the temporary data sheet seems to be copied as well - how do i get rid of that i only wish to copy from rows 2 down?

Also i want to insert the copied cells not paste them after the last row of data as the workbook has formulas at the bottom -

Thanks really appreciate the help

so i really i need to copy the data open the workbook

can you attach a copy of temp workbook?

to clarify which row are your headers on ?

It clarifies it but not sure why it does not work as it works with my workbooks, can you attach your files? What column are the formulas on, you say last row but do you really mean last row of a table or something similar?

Hi

Sorry steve i cannot attach the file atm -sorry

To explain, essentially you are adding the data to a larger list of data which has total formulas at the bottom - between the formulas and end of the list of data there are some empty rows

In the old excel 2003 one was able to copy rows and insert copied rows into another worksheet - that is not a feature in 2013 as it is NOT highlighted after copying the selection- it driving me crazy and that essentially is my problem here.

If i manually copy them and paste them whilst vba isnt on it works with vba its not highlighted :-(

Sorry, I have no experience with 2013 so i doubt i will be able to help with this particular issue.

I hope someone else more qualified can help with this.

I modified your original code to what I think you may want to achieve. See if it helps on a sample.

Please Login or Register  to view this content.

thnk you so much seems to be working now

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.