Macro Editing for New File Name

**christipper** · 03-13-2013, 06:44 AM

Hi,

I have recorded a macro to move data from one spreadsheet to specific cells in another, this all works fine for me.

My issue is that i want this to be done on a monthly basis but one of the file names will change each month and the original dat will be on a different row each month.

To give you a better idea. I will have sales data for several months in one file, at present i am down to row 135 which is for March 2013, the macro will currently copy this data and paste it to specific cells within the March accounts. However when we move into April i will now want the macro to pick up off row 136 and paste it to a completley different file, the April accounts.

This is the first part of the Macro that i have created.

Please Login or Register  to view this content.

Any ideas?

Cheers

Chris

(this is also posted on www.mrexcel.com http://www.mrexcel.com/forum/excel-q...o-editing.html)

**patel45** · 03-13-2013, 07:48 AM

Please Login or Register  to view this content.

**JOHN H. DAVIS** · 03-13-2013, 08:10 AM

Not sure I understand, but test this on a sample and see if it does what you want;

Please Login or Register  to view this content.

**JOHN H. DAVIS** · 03-13-2013, 08:13 AM

Not sure I understand, but test this on a sample and see if it does what you want;

Please Login or Register  to view this content.

**christipper** · 03-13-2013, 08:26 AM

Originally Posted by JOHN H. DAVIS

Not sure I understand, but test this on a sample and see if it does what you want;

Please Login or Register  to view this content.

Basically John,

I have one file, Sales Analysis V5 which has sales data in it and is updated on a monthly basis with all of the company sales. I want to be able to use a macro to transfer this info in my monthly accounts without having to flick back and forth between the two files and either copying and pasting or typing them in. The problem with using a macro to do this is that although the Sales Analysis filename doesn't change the row on which the sales numbers appear will, i.e. March 2013 is on row 135 so April 2013 will be row 136, so the Macro needs to pick up the last line of data not just row 135 all the time.

The second issue is where it pastes this copied data, as the accounts are saved down as a seperate file for each month Data 1303 for March Data 1304 for April, etc. so again the macro needs to know not to keep opening Data 1303 eacxh time and pasting the data there.

Chris

**JOHN H. DAVIS** · 03-13-2013, 08:31 AM

Did you test the macro I posted? I think it addresses those issues, but you need too test it to see if it does what you want.

**christipper** · 03-13-2013, 08:37 AM

No, it's failing on the first line with 'Run Time Error 438:' 'Object doesn't support this property or method'

**JOHN H. DAVIS** · 03-13-2013, 08:48 AM

Can attach the two files with sample data? Maybe I can figure out what the problem may be. Did you Test the macro from Post 2?

**JosephP** · 03-13-2013, 09:17 AM

as has already been pointed out twice to you your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

note: please do not respond with sarcasm-read the link, understand it and then please add links here to your cross-posts in other forums