adding data to the Lastcolumn

stirlingmw1

New Member
Joined
Jun 17, 2016
Messages
46
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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).

VBA Code:
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:

VBA Code:
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

stirlingmw1

New Member
Joined
Jun 17, 2016
Messages
46
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
I even tried

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

Regards

Steve
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
With Worksheets(WSName1)
   .Range(.Cells(3, LastColumn), .Cells(Lastrow, LastColumn)).Value = Worksheets("Requirements").Range("I3:I" & Lastrow).Value
End With
 

stirlingmw1

New Member
Joined
Jun 17, 2016
Messages
46
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
Fa
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome & thanks for the feedback.
 

stirlingmw1

New Member
Joined
Jun 17, 2016
Messages
46
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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??


VBA Code:
'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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Best to start a new thread. Thanks
 

Forum statistics

Threads
1,141,072
Messages
5,704,134
Members
421,328
Latest member
mippy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top