VBA To paste in the last column after data

[jondavis1987]

So i am making a vba to find the last column of a worksheet and add information into the column after it. I've used the following to find the last row before but i don't know how to tweak that to column.

.Range("A" & .Cells(Rows.Count, "A").End(xlUp).Row + 1).Resize(12, 1).Value = srcWB.Sheets("A").Range("G20").Value

 

[mumps]

This will give you the column number of the first unused column in row 1.

Sub Test()
    MsgBox Cells(1, Columns.Count).End(xlToLeft).Column + 1
End Sub

 

[jondavis1987]

This will give you the column number of the first unused column in row 1.

Sub Test()
    MsgBox Cells(1, Columns.Count).End(xlToLeft).Column + 1
End Sub

ah, sorry I should have expanded further. I'm going to be using the VBA to copy and paste a bunch of data into the last column so i'm trying to have the vba be dynamic to whatever the last column. The snippet of vba i pasted was doing the same thing but for last row instead of column.

 

[mumps]

It would be easier to help if you could use the XL2BB addin (icon in the menu) to post a screenshot (not a picture) of your sheet. Explain in detail what you want to do using a few examples from your data. Alternatively, you could upload a copy of your file to a free file sharing site and post a link to the file.

 

[jondavis1987]

My company seems to have the computer pretty well set where I'm not able to do add ins. But what I'm doing is trying to use VBA to copy data from one worksheet in a workbook and paste it to another worksheet in a workbook. All of the data from the original worksheet will go into one column of the new sheet. So if the destination sheet has data in columns A-G I want to paste that data into column E. So let's say in the source WS i have Cell A2. I want it to paste into E33 of the destination WS. But Cell A3 of the source WS will paste into E57 of the destination WS. So i need a VBA that will find that last column to start, offset by one, and allow me to paste into specific rows of that column.

 

[jondavis1987]

I figured it out. Thank you for the assist.

Option Explicit
Sub New_Mix_Design()
Dim srcWS As Worksheet
Dim destWS As Worksheet
Dim lColumn As Long

Set srcWS = Workbooks("Superpaves").Sheets("New Mix Design")
Set destWS = Workbooks("Superpaves").Sheets("Mix Design Information")
lColumn = destWS.Cells(1, destWS.Columns.Count).End(xlToLeft).Column + 1



    With destWS
    
    .Range("A" & .Cells(Rows.Count, "A").End(xlUp).Row + 1).Value = srcWS.Range("B1").Value
    .Cells(1, lColumn).Value = srcWS.Range("B1").Value
    .Cells(3, lColumn).Value = srcWS.Range("B1").Value
    .Cells(4, lColumn).Value = srcWS.Range("E3").Value
    .Cells(5, lColumn).Value = srcWS.Range("B2").Value
    .Cells(67, lColumn).Value = srcWS.Range("E12").Value
    .Cells(70, lColumn).Value = srcWS.Range("B20").Value
    .Cells(71, lColumn).Value = srcWS.Range("E22").Value
    .Cells(72, lColumn).Value = srcWS.Range("E23").Value
    .Cells(75, lColumn).Value = srcWS.Range("E13").Value
    .Cells(76, lColumn).Value = srcWS.Range("E14").Value
    .Cells(79, lColumn).Value = srcWS.Range("E11").Value
    .Cells(82, lColumn).Value = srcWS.Range("E8").Value
    .Cells(82, lColumn).Value = srcWS.Range("E7").Value
    .Cells(88, lColumn).Value = srcWS.Range("E17").Value
    .Cells(89, lColumn).Value = srcWS.Range("E18").Value
    .Cells(90, lColumn).Value = srcWS.Range("E16").Value
    .Cells(91, lColumn).Value = srcWS.Range("E19").Value
    .Cells(95, lColumn).Value = srcWS.Range("E20").Value
    .Cells(96, lColumn).Value = srcWS.Range("E21").Value
    .Cells(99, lColumn).Value = srcWS.Range("E9").Value
    .Cells(102, lColumn).Value = srcWS.Range("E15").Value
    .Cells(23, lColumn).Resize(12, 1).Value = srcWS.Range("D26:D37").Value
    .Cells(53, lColumn).Resize(12, 1).Value = srcWS.Range("F26:F37").Value
    .Cells(9, lColumn).Resize(12, 1).Value = srcWS.Range("B8:B19").Value
    .Cells(37, lColumn).Resize(12, 1).Value = srcWS.Range("B26:B37").Value

    End With

End Sub