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]

Even though Fluff has given you your answer, as I said I would post something your line should read

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

or

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

The DstWks.Columns.Count in the first code above is really a bit OTT as it would only come into play if DstWks referenced a different workbook and sheet (and really only if the workbook was an old version of Excel).

Just to try and clear up your confusion the syntax for Cells is

Cells(row number (without quotes), Column number (without quotes) or column letter (with quotes))

 

[John.McLaughlin]

Thanks gentlemen, the code works fine. I mis-stated my objective in the subject line, my apology.

I am trying to transpose the data found in my vertical column, and paste to a new worksheet horizontally in Row 1 at column A.

* Note to self, make image smaller next time.

 

[Fluff]

Try changing

SrcRng.Rows(r).Copy DstRng.Offset(N, 0)

to

SrcRng.Rows(r).Copy DstRng.Offset(,N)

 

[John.McLaughlin]

Try changing

SrcRng.Rows(r).Copy DstRng.Offset(N, 0)

to

SrcRng.Rows(r).Copy DstRng.Offset(,N)

That was it Fluff! I read/hacked for 2 nights before asking for help. I don't know if/when I would of found that. Thank you!

 

[John.McLaughlin]

Ugh, It pasted #ref ! error when encountering a formula. I've tried adding

 For r = 1 To SrcRng.Rows.Count                If SrcRng.Cells(r, "A") <> "" Then
                   SrcRng.Rows(r).Copy DstRng.Offset(, N).value
                   N = N + 1

and

For r = 1 To SrcRng.Rows.Count
                If SrcRng.Cells(r, "A") <> "" Then
                   SrcRng.Rows(r).Copy DstRng.Offset(, N).PasteSpecial(Paste:=xlValues)
                   N = N + 1

Going back and removing the dozen or so formulas on the source worksheet will create double entries that will be more confusing, instead of using the established "formulated" prefix to the item number. (The formulated item number denotes package deals we advertise)

Is there a work around? Or do I start over with new item numbers?

Ignorance is NOT bliss!

 

[Fluff]

Try

SrcRng.Rows(r).Copy 
DstRng.Offset(,N).PasteSpecial xlValues

 

[John.McLaughlin]

That was the fix! Many thanks!

Though I've learned alot searching out the answers, your help has been invaluable!

 

[Fluff]

Glad we could help & thanks for the feedback