Copy a range, but skip 1st and every 6th row, paste value to end of series

**rjnc** · 09-14-2015, 10:25 AM

Hoping to seek some assistance on the following.

I have a range that i want to copy however i want to format the data before pasting (spreadsheet attached)

Firstly i want to copy a field from Column D in the 1st and every 6th row and paste it to Column E, and loop it to the end of the series (illustrated with 'blue' arrows in the sheet).

Secondly i want to skip the 1st and every 6th row, when copying the range and paste it into the destination spreadsheet (the row i want to skip has a 'red' line through it)

Please find my code below.

Please Login or Register  to view this content.

Any assistance would be greatly appreciated!

**JOHN H. DAVIS** · 09-14-2015, 11:29 AM

Does this help?

Please Login or Register  to view this content.

**rjnc** · 09-14-2015, 12:07 PM

Originally Posted by JOHN H. DAVIS

Does this help?

Please Login or Register  to view this content.

Thanks John, the skipping of the row is working fine, however the script for copying of the Site Name (Column D) into Column E doesnt appear to be...
Being relatively novice will this be easy to incorporate into the existing piece of code that i already have?

Thanks!

**JOHN H. DAVIS** · 09-14-2015, 01:03 PM

Can you provide a sample of what your result sheet should look like. Actually I thought I had the Column E part right.

**rjnc** · 09-15-2015, 05:12 AM

Originally Posted by JOHN H. DAVIS

Can you provide a sample of what your result sheet should look like. Actually I thought I had the Column E part right.

Hi John,

I have attached both files and reattached the code. The code currently works, but without the skipping of the lines or the addition of the Site names.

In quick summary, the process is to copy the data from Report Sept15, paste it into the Master file after the last record and add the next period (max of column A + 1).

So any assistance with looping in the site name and then removing the 1st and 6th row would be greatly appreciated.

Please Login or Register  to view this content.

**JOHN H. DAVIS** · 09-15-2015, 03:15 PM

Maybe?

Please Login or Register  to view this content.

**rjnc** · 09-16-2015, 08:43 AM

Originally Posted by JOHN H. DAVIS

Maybe?

Please Login or Register  to view this content.

Very close, i have attached a screen shot, the only small amendments needed are the code seems to be adding some additional lines at the bottom and column A needs to be the 'max' of the column A + 1 not the predetermined number (the max function was in my original code), the last amendment is to only read in the extract file (Report Sept15.xlsx), eg. open and close the file in the background, without having to physically have it open for the macro to run.

Again appreciate your help on this one John.

**rjnc** · 09-17-2015, 04:08 AM

Originally Posted by JOHN H. DAVIS

Maybe?

Please Login or Register  to view this content.

Apologies forgot to attach, i have amended my post to include the attachment

**JOHN H. DAVIS** · 09-17-2015, 06:12 AM

I can't view screenshots. Can you provide a sample showing what you're getting, and then one showing what you require.

**rjnc** · 09-17-2015, 06:35 AM

Originally Posted by JOHN H. DAVIS

I can't view screenshots. Can you provide a sample showing what you're getting, and then one showing what you require.

No problem, please find the two files attached, one shows the current output based on the code supplied (file - Master File), the second is the ideal output (file - Master File Ideal). As mentioned column A needs to be dynamic (max of column + 1). This was referenced in my original code. Thanks!

**JOHN H. DAVIS** · 09-17-2015, 07:32 AM

Okay. Try this:

Please Login or Register  to view this content.

**rjnc** · 09-17-2015, 03:53 PM

Originally Posted by JOHN H. DAVIS

Okay. Try this:

Please Login or Register  to view this content.

Thanks John, absolutely spot on! Cheers!

**JOHN H. DAVIS** · 09-21-2015, 06:17 AM

You're welcome. Glad to help out and thanks for the feedback.

Copy a range, but skip 1st and every 6th row, paste value to end of series

LinkBack

Thread Tools

Rate This Thread

Display

Copy a range, but skip 1st and every 6th row, paste value to end of series

Re: Copy a range, but skip 1st and every 6th row, paste value to end of series

Re: Copy a range, but skip 1st and every 6th row, paste value to end of series

Re: Copy a range, but skip 1st and every 6th row, paste value to end of series

Re: Copy a range, but skip 1st and every 6th row, paste value to end of series

Re: Copy a range, but skip 1st and every 6th row, paste value to end of series

Re: Copy a range, but skip 1st and every 6th row, paste value to end of series

Re: Copy a range, but skip 1st and every 6th row, paste value to end of series

Re: Copy a range, but skip 1st and every 6th row, paste value to end of series

Re: Copy a range, but skip 1st and every 6th row, paste value to end of series

Re: Copy a range, but skip 1st and every 6th row, paste value to end of series

Re: Copy a range, but skip 1st and every 6th row, paste value to end of series

Re: Copy a range, but skip 1st and every 6th row, paste value to end of series

Thread Information

Users Browsing this Thread

Similar Threads

Cut and copy VBA - If range has value cut and paste to another cell - if Null Skip

[SOLVED] select & copy some range and past it to another sheet with sum

[SOLVED] Macro for COPY and PAST RANGE of data to ANOTHER workbook

[SOLVED] macro to Copy paste range skip zero values

Copy data from a certain range and past multiple times.

Copy last formula in column and past into range

hlookup to skip past first found 'lookup_value'

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions