adding data to the Lastcolumn

[stirlingmw1]

Morning All

I have a workbook where I am keeping track of ammunition allowances for my ships. Data is added to worksheets based on criteria set in a CmboBox. I can get the most of the data added to the correct worksheets using set columns and a Lastrow range, but there is one worksheet that data needs to be added to the next empty column rather than set columns. For the life of me I cannot seem to get my code to work.

I need the Data to be added to the WSName1 worksheet, where the first 2 columns have fixed data and the third will be where the new data starts to be added moving right. The Lastrow is defined by the qty of rows used in the "Requirements" worksheet (currently 28).

Lastrow = Worksheets("Requirements").Cells(Rows.Count, 2).End(xlUp).Row
LastColumn = Worksheets(WSName1).Cells(3, Columns.Count).End(xlToLeft).Column + 1

Worksheets(WSName1).Range(3, LastColumn & LastColumn & Lastrow).Value = Worksheets("Requirements").Range("I3:I" & Lastrow).Value

I have trawled the internet and tried multiple variations of the final row of code but nothing seems to work. The only thing that did work is when I used:

Worksheets(WSName1).Cells(3, LastColumn).Value = Worksheets("Requirements").Range("I3:I" & Lastrow).Value

but this only added data to the first Cell of the available column. Where am I going wrong

Thanks

Steve

 

[stirlingmw1]

I even tried

Worksheets(WSName1).Range(Cells(3, LastColumn), Cells(Lastrow, LastColumn)).Value = Worksheets("Requirements").Range("I3:I" & Lastrow).Value

Regards

Steve

 

[Fluff]

How about

With Worksheets(WSName1)
   .Range(.Cells(3, LastColumn), .Cells(Lastrow, LastColumn)).Value = Worksheets("Requirements").Range("I3:I" & Lastrow).Value
End With

 

[stirlingmw1]

Fa

How about

With Worksheets(WSName1)
   .Range(.Cells(3, LastColumn), .Cells(Lastrow, LastColumn)).Value = Worksheets("Requirements").Range("I3:I" & Lastrow).Value
End With

Fantastic, works a treat.

Regards

Steve

 

[Fluff]

You're welcome & thanks for the feedback.

 

[stirlingmw1]

You're welcome & thanks for the feedback.

Fluff

I am trying to add to this piece of code so that it checks to see whether the data is already present. If it isnt it then adds the data to the next available column. I am using the following code which isnt quite working as the data is added to the next available column anyway, am I on the right lines??

'Checks to see if the 3 top cells in the worksheet WSName1 contains the same data as in the CmboUnit, CmboApril Comboboxes and the 3rd cell has the work April in it
If Worksheets(WSName1).Cells(1, LastColumn).Value = CmboUnit.Value And _
                Worksheets(WSName1).Cells(2, LastColumn).Value = CmboApr.Value And _
                Worksheets(WSName1).Cells(3, LastColumn).Value = "April" Then
'if true append the data from row 5 to lastrow
                Worksheets(WSName1).Cells(Column, Lastrow).Value = Worksheets("Requirements").Range("I3:I" & Lastrow).Value
                Else
'if false add the data to a new column
                    With Worksheets(WSName1)
                        .Cells(1, LastColumn).Value = CmboUnit.Value
                        .Cells(2, LastColumn).Value = CmboApr.Value
                        .Cells(3, LastColumn).Value = "April"
                        .Range(.Cells(5, LastColumn), .Cells(Lastrow, LastColumn)).Value = Worksheets("Requirements").Range("I3:I" & Lastrow).Value
                    End With
                End If

or should I ask this as a new thread?

Thanks

Steve

 

[Fluff]

Best to start a new thread. Thanks

 

[stirlingmw1]

Best to start a new thread. Thanks

No problem, thanks for getting back to me so quickly.

Steve

 

[Fluff]