Macro to copy range from one worksheet to another into the appropriate month

[reece1984]

Hi All,

I've been working on this for while, i'm sure its quite simple for some of you.

The challenge is to have a macro which copies a range from an input worksheet and pastes it onto an "archive" worksheet into the appropriate month's column. The size of the range will be standard.

I expect that this can be done with an offset, which can move along the months on the archive sheet the amount of months that the current month is from the first month on the archive sheet. Or perhaps a lookup?

I've been trying different things in vba but cannot get it to work.

Any help would be really appreciated because this would solve a problem lots of my files.

J

 

[andrewkard]

Where is a problem? Can we see your code?

 

[reece1984]

Hi,

Thanks for your reply.

I have progressed a little since my post. Here is my code:

Sub archive1()

Sheets("input").Select
Range("C6:C10").Cut
Sheets("archive").Select
Range("C6:C10").Offset(0, Range("B3")) = ActiveSelection.Paste

End Sub

The part that says ActiveSelection.Paste does not work but it is there to show you what I'm trying to do.

Any ideas?

 

[andrewkard]

Do you want to paste your data in Range("B3")? Maybe so:

Sub archive1()
    Sheets("input").Select
    Range("C6:C10").Copy
    Sheets("archive").Select
    Range("B3").Select
    ActiveSheet.Paste
End Sub

 

[andrewkard]

or so:

Sub archive1()
    Worksheets("archive").Range("C6:C10") = Worksheets("input").Range("B3:B7").Value
End Sub

 

[reece1984]

Sorry, I didnt explain that very well at all but your code really helped.

For the purposes of anybody reading this post.

B3 is a number which is linked to a combo box to signify the current month.

So I wanted to copy a range on "input" worksheet, and then paste it into "archive worksheet" but offset the number of months from january.

The code which now appears to work great is:

Range("C6:C10").Offset(0, Range("B3")) = Worksheets("input").Range("C6:C10").Value

Andrew, Thanks very much.


This thread is now closed.