0

Finds each worksheet with 2021 in the name and copies the range indicated. This script finds the last used cell address in an attempt to get the last column for Sheets("Monthly"). Then uses the last column to paste from the selected range of worksheet into Sheets("Monthly").

Why is it not appending to last column?

Sub Monthly2()

Dim ws As Worksheet
Dim Monthly As Worksheet
Dim LastRowM As String
Dim ColumnLetter As String

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "2021*" Then
        LastRowM = Sheets("Monthly").Range("A1").End(xlToLeft).Address
        ColumnLetter = Split(LastRowM, "$")(1)
        ws.Range("B3:H61").Copy Destination:=Sheets("Monthly").Range(ColumnLetter & "2")
        Exit For
    End If
Next ws

End Sub
Corrie Byrd
  • 29
  • 4
  • What is the result in the Immediate Window of `Debug.Print ColumnLetter`? – BigBen Apr 12 '21 at 17:39
  • XFD is the corresponding Column Letter – Corrie Byrd Apr 12 '21 at 17:46
  • Use `End(xlToLeft)` to find the last column, as demonstrated in the linked answer. – BigBen Apr 12 '21 at 17:48
  • Thanks, i'll try it. – Corrie Byrd Apr 12 '21 at 17:50
  • So that fixed that problem, but it's only copying the first sheet of cells B3:H61 and pasting those into "Monthly" and none of the other sheets. – Corrie Byrd Apr 12 '21 at 17:52
  • @BigBen: That's not the real problem of the OP. The problem is that the address is split and the column-letter is read from element 1 - that is the 2nd part of the address (as split returns a 0-based array), however the column needs to be taken from element 0. – FunThomas Apr 12 '21 at 17:52
  • @FunThomas - "XFD" is the very last column on the page so most certainly the problem - using `xlToLeft` is the correct approach and as OP noted, fixes the issue. – BigBen Apr 12 '21 at 17:53
  • Please reread the linked duplicate: `LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column`. – BigBen Apr 12 '21 at 17:55
  • Sorry, I was wrong - element(0) is empty. I never use this strange method to get a range – FunThomas Apr 12 '21 at 17:55
  • @FunThomas - yes I agree, using `Split` and the column letter is really really clunky. – BigBen Apr 12 '21 at 17:56
  • I can see it looping through, copying and pasting, but it's not appending to the last column in "Monthly" – Corrie Byrd Apr 12 '21 at 17:56
  • As mentioned, please reread the linked duplicate. You can't just change `xlToRight` to `xlToLeft`. The `.Cells(1, .Columns.Count)` is also necessary. – BigBen Apr 12 '21 at 17:57
  • You need to put something in `Range(ColumnLetter & "1")` otherwise `LastRowM` never changes. – CDP1802 Apr 12 '21 at 18:07
  • CDP1802 - Wouldn't For Each tell it to loop through "Monthly" each time to get a new last column? – Corrie Byrd Apr 12 '21 at 18:09
  • 1
    Yes last column in row 1 but you are pasting into row 2. – CDP1802 Apr 12 '21 at 18:27

0 Answers0