xltoright question

[largeselection]

Just a quick one. How can I work with xltoright to fill a space beneath a row of data?

I have a column of formulas that I have copied and I need to just spread it as far to the right as the row of data directly above it.

Here's what I use to copy:
Selection.Copy
Range(Selection, Selection.Offset(795, 0)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Copy


and then I had
Range(Selection, Selection.Offset(0, 100)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

to copy it to the right, but I'm finding that my data doesn't always have 100 columns and it can vary. So how can I add that? I thought maybe something with selecting a cell in that row then xltoright and then offsetting? but I'm not sure how I would work that into range (selection, selection) since I need to select the whole block.

 

[Domski]

Using Select and Selection is not usually advised. Are you selecting the range to copy manually or using code? If code can you post the full version as it can probably be re-written to avoid it.

Dom

 

[Andrew Poulsom]

Is it?

    Selection.Copy
    Range(Selection.Offset(-1), Selection.Offset(-1).End(xlToRight)).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[largeselection]

Sub compile()

Sheets("DATA INPUT").Select
Range("B1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("DATA").Select
Range("XFD2").Select
Selection.End(xlToLeft).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("DATA INPUT").Select
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("DATA").Select
Range("XFD3").Select
Selection.End(xlToLeft).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("DATA INPUT").Select
Range("B3").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("DATA").Select
Range("XFD4").Select
Selection.End(xlToLeft).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("XFD5").Select
Selection.End(xlToLeft).Offset(0, 1).Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(INDEX('DATA INPUT'!R4C:R10485C,MATCH(RC1,DATA INPUT'!R4C1:R10485C1,FALSE),1)),"""",INDEX('DATA INPUT'!R4C:R10485C,MATCH(RC1,'DATA INPUT'!R4C1:R10485C1,FALSE),1))"
ActiveCell.Select
Selection.Copy
Range(Selection, Selection.Offset(795, 0)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Copy
Range(Selection, Selection.Offset(0, 100)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

So basically it goes to the data input sheet copies three rows of data (which may be different lengths depending on what input data is on the sheet) and then pastes it into a "data" sheet. These are the headings. Then it does a lookup in the "data" sheet which references the data in the "data input" sheet. Then it copies that lookup function and pastes it into the 800 or so cells beneath it, then copies that column of cells and pastes it as far as the initial headers (that were copied/pasted) go out.

I know it can probably be more elegant, but I'm just a beginner