Consecutive Numbers VBA Help

[jamesfbeard123]

Hi All,

I am in need of some help!

On one sheet I have a list of numbers listed in consecutive order e.g.

1
2
3
4
5
6


On the second page I have a similar list of consecutive numbers but I need my macro to update the second sheet with the additional consecutive numbers that ar eon shete one.

e.g. sheet 2 could

1
2
3

So sheet 2 would need to update the column with 4, 5, 6

Is there an easy way to do this without copying and pasting?

Thanks,

James

 

[gallen]

You mean without copying and pasting manually or without copying/pasting in VBA? opy and paste via VBA seems the right way about it though there are several possibilities

 

[gallen]

This code will get the last used number and then generate sequential numbers on sheet 2 starting from 1:

Sub DuplicateNumbers()

'This sub assumes the values are in COl A on both sheets and start from row 1
Dim wsFrom As Worksheet
Dim wsTo As Worksheet


Dim lLastNumber As Long, l As Long


    'change to names of sheets you are coptying from and to
    Set wsFrom = ThisWorkbook.Sheets("Sheet1")
    Set wsTo = ThisWorkbook.Sheets("Sheet2")
    
    'Get the value in the last used text box of column A
    lLastNumber = wsFrom.Range("A" & Rows.Count).End(xlUp).Value
    
    'clear column A
    wsTo.Range("A:A").Clear
    'fill column A on sheet 2 with 1 to last number on sheet 1
    For l = 1 To lLastNumber
        wsTo.Range("A" & l) = l
    Next
    
End Sub

 

[Peter_SSs]

Is there an easy way to do this without copying and pasting?

If you could elaborate on why that method is not acceptable, it might help produce the best result.

In the interim, assuming a start in cell A1 of each sheet, does this do what you want?

Sub MatcLists()
  Dim lr As Long
  
  lr = Sheets("Sheet 1").Range("A" & Rows.Count).End(xlUp).Row
  Sheets("Sheet 2").Range("A1:A" & lr).Value = Sheets("Sheet 1").Range("A1:A" & lr).Value
End Sub

 

[jamesfbeard]

I will have to try both and see the results!

The reason we cant copy is that the second sheet has specific cells data next to the numbers and this data may not be on th first original sheet at a specific time it is run.

 

[jamesfbeard]

This code will get the last used number and then generate sequential numbers on sheet 2 starting from 1:

Sub DuplicateNumbers()

'This sub assumes the values are in COl A on both sheets and start from row 1
Dim wsFrom As Worksheet
Dim wsTo As Worksheet


Dim lLastNumber As Long, l As Long


    'change to names of sheets you are coptying from and to
    Set wsFrom = ThisWorkbook.Sheets("Sheet1")
    Set wsTo = ThisWorkbook.Sheets("Sheet2")
    
    'Get the value in the last used text box of column A
    lLastNumber = wsFrom.Range("A" & Rows.Count).End(xlUp).Value
    
    'clear column A
    wsTo.Range("A:A").Clear
    'fill column A on sheet 2 with 1 to last number on sheet 1
    For l = 1 To lLastNumber
        wsTo.Range("A" & l) = l
    Next
    
End Sub

This is pretty much there but instead of deleting the second sheets data I need it to just carry on from the last value in sheet 2.


So Sheet 1:

1
2
3
4
5


Sheet 2:
1
2
3


The macro needs to just add 3, 4 and 5 to sheet two instead of starting all over again.

Thanks for everyones help!

 

[Peter_SSs]

The reason we cant copy is that the second sheet has specific cells data next to the numbers and this data may not be on th first original sheet at a specific time it is run.

What would that have to do with pasting data in column A only? Whatever is next to that would just remain.

I will have to try both ..

What happened when you tried the other one?

Sheet 2:
1
2
3


The macro needs to just add 3, 4 and 5 to sheet two

That would give on sheet two:
1
2
3
3
4
5

What's the logic for having two "3"s?