Finding last column, pasting data

[John.McLaughlin]

I am reusing/converting old code that found the last row.

The old code is used on my List of goods and services. When the quantity column is greater than zero, it summarizes several columns on the worksheet “invoice”

I am trying to modify that code to instead summarize one column from the list of goods and services (ITEM NO/COL I) and paste it transposed across row A of the new worksheet “packlist”.

Specifically, the code stops at;

 LastColumn = DstWks.Cells(Column.Count, "1").End(xlUp).Column

Sub PostPackList()
 
    Dim DstRng As Range
    Dim DstWks As Worksheet
 
'    Dim LastRow As Long
    Dim LastColumn As Long
 
    Dim N As Long, r As Long
    Dim SrcRng As Range
    Dim SrcWks As Worksheet
   
    Application.ScreenUpdating = False
    Sheets("PACKLIST").Select
     
   
      ' Assign the Worksheets
        Set SrcWks = Worksheets("SERVICES")
        Set DstWks = Worksheets("PACKLIST")
       
      ' Get all cells in the Source Range starting with row 2
       
        Set SrcRng = SrcWks.Range("I2:I2")
        LastRow = SrcWks.Cells(Rows.Count, "I").End(xlUp).Row
        If LastRow < SrcRng.Row Then Exit Sub Else Set SrcRng = SrcRng.Resize(LastRow - SrcRng.Row + 1, 1)
       
      ' Find the next empty column in the Destination Range starting at column A
        Set DstRng = DstWks.Range("A1")
        LastColumn = DstWks.Cells(Column.Count, "1").End(xlUp).Column
        Set DstRng = IIf(LastColumn < DstRng.Column, DstRng, DstRng.Offset(LastColumn - DstRng.Column + 1, 0))
       
          ' Copy the Source cells to the next empty Destination column if the Source Cell in "A" is not empty
            For r = 1 To SrcRng.Rows.Count
                If SrcRng.Cells(r, "A") <> "" Then
                   SrcRng.Rows(r).Copy DstRng.Offset(N, 0)
                   N = N + 1
                End If
            Next r
  
 Sheets("PACKIST").Select
       

ActiveWorkbook.Save
Application.CutCopyMode = False
Application.ScreenUpdating = True
 
End Sub

Thanks in advance, I am barely above newbie, but a really good neighbor ?

 

[MARK858]

Try changing

End(xlUp)

To

End(xlToLeft)

DstWks.Cells(1, Column.Count).End(xlToLeft).Column

 

[John.McLaughlin]

I changed to End(xlToLeft) in this line

LastRow = SrcWks.Cells(Rows.Count, "I").End(xlToLeft).Row

I get a compile error, invalid or unqualified reference on this line?

LastColumn = DstWks.Cells(1, .Columns.Count).End(xlToLeft).Column

DstWks.Cells(1, Column.Count).End(xlToLeft).Column

it opened the ListModules module compile error, User-defined typer not defined

I then modified your line to read

 [/COLOR]LastColumn = DstWks.Cells(1, Column.Count).End(xlToLeft).Column

same error

 

[MARK858]

Put DstWks in front of .columns.count. The column.count should have an s.

 

[John.McLaughlin]

please forgive my ignorance, I added DstWks before the Columns count and corrected "columns", it didn't work

DstWks.Cells(DstWks.Columns.Count, "1").End(xlUp).Columns

 

[MARK858]

Now you have reversed the columns and rows! It is
Cells(1, DstWks.Columns.Count)

 

[John.McLaughlin]

Thank for your help, but I am more confused than ever, the line is red

 

[Fluff]

It needs to be like

Set DstRng = DstWks.Range("A1")
LastColumn = DstWks.Cells(1, Columns.Count).End(xlToLeft).Column
Set DstRng = IIf(LastColumn < DstRng.Column, DstRng, DstRng.Offset(LastColumn - DstRng.Column + 1, 0))

 

[MARK858]

I'll post the complete line when I get in rather than doing it on my phone.

 

[John.McLaughlin]

It needs to be like

Set DstRng = DstWks.Range("A1")
LastColumn = DstWks.Cells(1, Columns.Count).End(xlToLeft).Column
Set DstRng = IIf(LastColumn < DstRng.Column, DstRng, DstRng.Offset(LastColumn - DstRng.Column + 1, 0))

I'll post the complete line when I get in rather than doing it on my phone.

Thank you both!!!


Fluff, that took me back to listing the results down column A, I am trying to list the results across row 1 starting at column A.


Mark858


Wow, a phone reply? That is killer! Sorry my lack of knowledge takes a keyboard, thank you very much!

I have to go wrestle a bear now, but will be back this evening to try again.