Copy Over to last Column

gheyman

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,338
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:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Try:

Code: 
Range("F2", Cells(WBSLstRow, WBSLatCol)).FormulaR1C1 = _
 
Last edited:
Upvote 0
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
 
Upvote 0
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)"
 
Upvote 0
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:
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question
Copy Last column to Last row
Replies
3
Views
441
MARK858
MARK858
S
  • Solved
Compare column A with several columns and copy the non-existent one
Replies
3
Views
186
Trebor76
T
S
  • Solved
variable formula
Replies
3
Views
385
DanteAmor
DanteAmor
J
  • Solved
Code to copy over row if certain criteria is met
2
Replies
18
Views
552
mumps
mumps
T
  • Question
Fill Down In VBA
Replies
2
Views
356
Skyybot
Skyybot

Forum statistics

Threads
1,214,515
Messages
6,119,973
Members
448,933
Latest member
Bluedbw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top