Copying used range from one sheet to another

[kenadams378]

Morning,

I'm trying to copy used range data from one tab to another tab, using the header as an identification as to where to copy the data to and from. I need to bare in mind that there maybe a change in data range so need to code it to copy the used data rather than cell range?

Anyone help,

I've attached a sample of the data.

[arlu1201]

I am not able to understand your question. Maybe you can try explaining using your attached file?

[kenadams378]

Apologies,

On sheet 1 i have the source data, i need to copy this to sheet 2 directly under each of the headings.

When i copy the data i need to code it so if there is more data than what i have this month it will copy it all rather than the range of data i have this month?

Does that make more sense?

[kenadams378]

Did that help?

[arlu1201]

Yes, will the column order in sheet2 always be the same as in sheet1?

[kenadams378]

Yes it will, the only thing that will change will be the amount of data.

[arlu1201]

Try this code

Please Login or Register  to view this content.

Put the code in a standard module.

[kenadams378]

Thanks, works very well.

Thank you

[kenadams378]

Silly Question.

What does this bit mean;

Please Login or Register  to view this content.

[kenadams378]

Apologies to come back to you on this, some of the columns will change postion in the data too, is it possible to code this to copy of columns seperatly based on column header?

[arlu1201]

Silly Question.

What does this bit mean;

Please Login or Register  to view this content.

I am using a variable called lrow which means lastrow. It checks which is the last row of data in your sheet. Long is just to denote how much space in memory it needs.

[arlu1201]

Apologies to come back to you on this, some of the columns will change postion in the data too, is it possible to code this to copy of columns seperatly based on column header?

This is what i had asked you in post5. Can you explain some more regarding the columns changing position? Do you mean to say the 12 columns will be there but in mixed order? Or there are chances that some of the columns might not be there at all?

[kenadams378]

Was does the DIM mean?

[arlu1201]

Its a programming term used to declare variables.

[kenadams378]

All the columns will be there but there will other columsn within the sheet that will have forumla's in them.

I have attached an example.

This is what i had asked you in post5. Can you explain some more regarding the columns changing position? Do you mean to say the 12 columns will be there but in mixed order? Or there are chances that some of the columns might not be there at all?

[arlu1201]

You have only provided me one sheet. Please upload the sheet containing the data that needs to be copied to this sheet.

[kenadams378]

As requested

[kenadams378]

Did you get chance to ook at the extra attachment?

[arlu1201]

Will look into this right now.

[kenadams378]

Thank you, apologies for the duplicate post.

[kenadams378]

Any luck with this, is it possible?

[arlu1201]

Can you explain where the entries from sheet1 should go in sheet2? For e.g. sheet1 column A should go to column A in sheet2, sheet1 column B should go to sheet2 column C,etc.

[kenadams378]

Each column should be matched using the headers of each column so for example;

Funding to funding
Amount to Amount

[kenadams378]

Has that helped?

[arlu1201]

Use this code

Please Login or Register  to view this content.

Put it in a standard module.

[kenadams378]

Morning,

I'm having an issue with this code

Please Login or Register  to view this content.

The error message is 'Application-defined or object defined error?

Any ideas?

[arlu1201]

Attach the file that you are using with this code.

[kenadams378]

The file i'm using is over 70mb

[arlu1201]

The code that i provided in post 25 works with the attachment that you provided in post 23. You have edited the code, hence it could be causing the error. What are you attempting to copy using these lines

Please Login or Register  to view this content.

[kenadams378]

I'm trying to copy columns of data from one sheet to another.

The columns range in terms of amount of data and position on the final worksheet.

[arlu1201]

Yes, you are right. What error did you get when you tried the code in post 25? Also, you added new lines of code as in post 29. What do you want to do in addition to the code i provided, by running those code lines?