Copy specific range to offset location

**Mister P** · 01-21-2009, 01:56 AM

I would appreciate some ones help to correct a macro I'm trying to write. The macro copies 3 columns from Sheet1 to Sheet2 in a selected location. The 3rd column copied needs to be pasted in a different column in Sheet2.

Please Login or Register  to view this content.

Above copies Columns A, B & H (ranges) from Sheet1 and pastes to A, B & C columns in Sheet2 or depending on what cell was selected in Sheet 2 (1st, 2nd & 3rd columns). The requirement is to to have H pasted to Sheet2 F (6th column).

I have tried several Target.Offset statements but have had no success. I've attached my sample sheet with what I have so far.

Thanks in advance

**JBeaucaire** · 01-21-2009, 03:28 AM

Try this instead:

Please Login or Register  to view this content.

**Mister P** · 01-21-2009, 03:52 AM

JB

Tried your code and yes it pastes in the correct columns but what I need is that the resulting paste start to a selected cell on Sheet2. If I select E17 on Sheet2 and run the macro the resulting paste should be column E, F & J starting at row 17.

I will be using this macro to copy specified columns from a variety of sheets to assemble one combined sheet.

Thanks

**JBeaucaire** · 01-21-2009, 04:27 AM

That second example doesn't match the request from the first. The first was A,B,F (nine cells) copied to A,G,H (offset by 1).

Now your E17 example is a 1 to 1 paste? What's different? Are the ranges always nine cells? Will an xlDown work to select a range of cells, meaning thing there's a break in the data? Or does it have to be manually set?

**Mister P** · 01-21-2009, 05:15 AM

JB

I'm not explaining this well enough. Both requests are for the same columns in Sheet1 to be copied and pasted to Sheet2. My original code I included, would allow me (or any user) to select a specific column & cell in Sheet2 before running the macro. The resulting paste would start at the column & cell which was selected. I want to maintain this feature. The portion I'm having problems with is having the macrco offset the copied H to a position 5 columns to the right of where the A column pasted. In my second post the paste if started at E17 the first column pasted would be E, F J or 1st, 2nd and 6th columns. If the code you posted could be modified to allow the user where the paste should start on Sheet2 the problem would be solved.

Hope this clarifies the request.

**DonkeyOte** · 01-21-2009, 05:35 AM

How about:

Please Login or Register  to view this content.

Assumes you're running this with Sheet2 being the active sheet... can be altered if not.

**Mister P** · 01-21-2009, 07:29 AM

Thank you DonkeyOte

Tried your code and it works perfectly. This is exactly how I wanted the macro to be applied. I have considerable experience in programming but am really new to VBA and need assistance from time to time. Really appreciate your help.

Cheers

Copy specific range to offset location

LinkBack

Thread Tools

Rate This Thread

Display

Copy specific range to offset location

Works great

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions