Copy Over to last Column

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,756
Office Version
  1. 365
Platform
  1. Windows
Copy Down to Last row AND copy over to Last Column. I cant figure out how to put both last column and last row in my Formula.

How do you write this formula range so that it starts in F2 and copies down to the last Row (WBSLstRow and across to the last Column (WBSLatCol)?

Is it ("F2" & WBSLastCol & WBSLastCol)? I get an error


Code:
 WBSLstRow = Sheet8.Range("A" & Rows.Count).End(xlUp).Row
    WBSLatCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    
        Range("F2:Z" & WBSLstRow).FormulaR1C1 = _
        "=IFERROR(IF(RC5=""Child"",(SUMIFS('54_TPL_01_02'!C12,'54_TPL_01_02'!C7,R1C,'54_TPL_01_02'!C3,RC1)),IF(RC5=""Parent"",SUMIF(R[1]C2:R5000C2,RC1,R[1]C:R5000C),0)),0)"
 
Last edited:

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,340
Try:

Code:
Range("F2", Cells(WBSLstRow, WBSLatCol)).FormulaR1C1 = _
 
Last edited:

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,756
Office Version
  1. 365
Platform
  1. Windows
the code I have doesn't like "WBSLstCol = Sheet8.Range(1 & Columns.Count).End(xlToLeft).Column"

Code:
Sub WBS_SUMMARY()

Dim BOELst As Long
Dim WBSLst1 As Long
Dim WBSLstRow As Long
Dim WBSDLst As Long
Dim WBSLstCol As Long

BOELst = Sheet9.Range("C" & Rows.Count).End(xlUp).Row

    Sheet8.Select
    Cells.Select
    Selection.ClearContents

    Sheets("54_TPL_01_02").Select
    Range("G11:G" & BOELst).Select
    Selection.Copy

    Sheets("WBS_SUMMARY").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    ActiveSheet.Range("$A:$A").RemoveDuplicates Columns:=1, Header:=xlNo

    ActiveWorkbook.Worksheets("WBS_SUMMARY").Sort.SortFields.Add Key:=Range("A:A") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("WBS_SUMMARY").Sort
        .SetRange Range("A:A")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    WBSLst1 = Sheet8.Range("A" & Rows.Count).End(xlUp).Row
    
     Range("A1:A" & WBSLst1).Select
    Selection.Copy
    Range("F1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 90
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
        
        Range("A1:A" & WBSLst1).Select
        Selection.ClearContents

WBSDLst = Sheet2.Range("C" & Rows.Count).End(xlUp).Row

    'WBS NUMBER
    Sheet2.Select
    Range("C9:C" & WBSDLst).Select
    Selection.Copy
    Sheet8.Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    'WBS TITLE
    Sheet2.Select
    Range("G9:G" & WBSDLst).Select
    Selection.Copy
    Sheet8.Select
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    'WBS Level
    Sheet2.Select
    Range("D9:D" & WBSDLst).Select
    Selection.Copy
    Sheet8.Select
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    'RELATION
    Sheet2.Select
    Range("O9:O" & WBSDLst).Select
    Selection.Copy
    Sheet8.Select
    Range("E2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    'PARENT WBE
    Sheet2.Select
    Range("P9:P" & WBSDLst).Select
    Selection.Copy
    Sheet8.Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    Sheet8.Select
     Range("A1").FormulaR1C1 = "WBS NUMBER"
     Range("B1").FormulaR1C1 = "PARENT"
     Range("C1").FormulaR1C1 = "TITLE"
     Range("D1").FormulaR1C1 = "RELATION"
    Range("E1").FormulaR1C1 = "PARENT"
      
      Range("A1:E1").Select
    Selection.Font.Underline = xlUnderlineStyleNone
    Selection.Font.Underline = xlUnderlineStyleSingle
    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("A:E").EntireColumn.AutoFit
    Columns("A:C").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("D:E").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    Range("A1").Select
    
    'Color Fonts PLC
    Range("F1:BA1").Select
    With Selection.Font
        .Color = -3394816
        .TintAndShade = 0
    End With

    
  '  WBSLstRow = Sheet8.Range("A" & Rows.Count).End(xlUp).Row
    WBSLstCol = Sheet8.Range(1 & Columns.Count).End(xlToLeft).Column


    
    Range("F2", Cells(WBSLstRow, WBSLstCol)).FormulaR1C1 = _
        "=IFERROR(IF(RC5=""Child"",(SUMIFS('54_TPL_01_02'!C12,'54_TPL_01_02'!C7,R1C,'54_TPL_01_02'!C3,RC1)),IF(RC5=""Parent"",SUMIF(R[1]C2:R5000C2,RC1,R[1]C:R5000C),0)),0)"
        
        
 '   Columns("F:" & WBSLstCol).Select
  '  Selection.ColumnWidth = 6
  '  Range("A2").Select


End Sub
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,756
Office Version
  1. 365
Platform
  1. Windows
Got it

WBSLstRow = Sheet8.Range("A" & Rows.Count).End(xlUp).Row
WBSLstCol = Sheet8.Cells(1 & Columns.Count).End(xlToLeft).Column


Range("F2", Cells(WBSLstRow, WBSLstCol)).FormulaR1C1 = _
"=IFERROR(IF(RC5=""Child"",(SUMIFS('54_TPL_01_02'!C12,'54_TPL_01_02'!C7,R1C,'54_TPL_01_02'!C3,RC1)),IF(RC5=""Parent"",SUMIF(R[1]C2:R5000C2,RC1,R[1]C:R5000C),0)),0)"
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,340
I think your WBSLstCol formula is off, try:

Code:
[COLOR=#333333]WBSLstCol = Sheet8.Cells(1[/COLOR][COLOR=#ff0000],[/COLOR][COLOR=#333333] Columns.Count).End(xlToLeft).Column[/COLOR]


where the 1 is the row where you want to find the last used column. If you want to find the furthest column used by any row, then try:

Code:
[COLOR=#333333]WBSLstCol = Sheet8.UsedRange.Columns.Count + Sheet8.UsedRange.Column - 1[/COLOR]
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,114,236
Messages
5,546,672
Members
410,753
Latest member
Ian R
Top