PDA

View Full Version : (Bit Rusty, Need Help) Copy & Paste for Adjustable Cell Range (Horizontal to Stacked)



Lucas_NYC
10-07-2014, 08:00 PM
Hi everyone, I haven't programmed with VBA in a long time and would love some help with a project.. Yes, it's a work project.. But this simple code should save me a several hours tomorrow. Plus, I have a feeling some of you would be able to make an amazing contribution :)

Below are two screenshots, one from the raw data and then one of the data after the macro. My current macro isn't very user friendly as I have to go in and adjust the cell range for copying & pasting in lots of lines of coding. I need to do this as the cell range depends on how many rows of output I have from a report (this could go as high as 1,000 rows). Columns B-F need to be copied and placed below the original data along with one set of invoice info (the # cell & date cell) which needs to be pasted beside the new Columns B-F (see pictures below).

Below is the 'input screen' where I have output from a downloaded file. I need the information in a more (vertically) searchable format (as I have to find all invoices with-in a specific month), so basically I need columns that are sortable by (individual) invoice dates.

12370




What I need it to do:
1. Copy/Paste all of 'column B-F' and 'column G' & 'column H'
2. Copy all of 'column B-F' (below the first set of date) and 'column I' & 'column J'
3. Repeat copy/paste method (keeping columns B-C data & putting an invoice set (#&date) beside it)

This is what the output looks like:


12371


If you even just know pieces of code to help with how to adjust cell ranges with a formula [I don't quite understand off.set(?)]. I've looked around the internet and forums, but nothing seems adaptable when I had thought it would be something very common. If you are able to write the full code, that would also be very appreciated. If you have any questions please post or PM me.

Thanks,
Lucas

snb
10-08-2014, 01:31 AM
Sub M_snb()
sn=activesheet.cells(1).currentregion

for j=1 to 4
sp=application.index(sn,evaluate("row(1:"& ubound(sn) &")"),array(2,3,4,5+2*j,6+2*j))
sheet2.cells(rows.count,1).end(xlup).offset(1).resize(ubound(sn),5)=sp
next
End Sub