xltoright question

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
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

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Is it?

Code:
    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

Active Member
Joined
Aug 4, 2008
Messages
353
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 :biggrin:
 

Forum statistics

Threads
1,081,560
Messages
5,359,609
Members
400,538
Latest member
leon_oscar

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top