Copying data into next available column to the right using macros?

[sh0ckwav3s]

Morning all, new user here with extremely basic knowledge on all this as only just started using them.

ive created s spreadsheet to copy data from a from 2 columns, and then paste it into another worksheet.
Ive got it setup atm so it copies the data, then creates 2 empty columns to the left of the data ready for the next data to be copied over, so the data goes in order from right to left.

What i would like is for the data to go from left to right, but i cant figure out how to make the macro put the data into the next available column on the right without overwriting the original data.

Sorry if this doesnt make sense, like i said, i am only just getting into it to help make my job slightly easier & quicker
quick screenshots of my basic code, plus an example of it, so week 26 is after test2, but i would like it going test2, test1, then week 26, if its possible

Cheers for any responses

 

[RobP]

Hi, you can use this statement to find last column used (assuming columns to the right of your data are empty:

lColumn = ws.UsedRange.Column + ws.UsedRange.Columns.Count - 1

So if you know that say lColumn is 4 in this case, and you want to paste data in col 5 ("E") you can say :

Sheets("PD Swab Results").Select
lColumn = ws.UsedRange.Column + ws.UsedRange.Columns.Count - 1
Range("A" & lColumn+1).Select
Activesheet.Unprotect
Activesheet.Paste

 

[mumps]

It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet.
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[RobP]

Oops, I hadn't tested my above code.

Try this one instead:

Sheets("PD Swab Results").Select

lColumn = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column 'where 1 = the row number to look in for colmns used

ActiveSheet.Range.Cells(4, lColumn + 1).Select

ActiveSheet.Unprotect

ActiveSheet.Paste