I have a simple workbook. three tabs -
TAB 1"Control" where the macro can be triggered,
TAB2 "Count" where all the contract ID's are dropped, in cell L1 the total number of lines of contracts are multiplied by all the months in a given time period. E.g for 2014-2020 by month there are 84 months, there are 408 contracts, so by adding 1 to this number, we get the number of rows including titles we require. In Cell o1 we have return back count of how many rows there are in the tab 3 "DATA" ( including the row titles) which is where i would like to copy the 408 contacts repeatedly , until I have the required number of lines to have a contract line for each month of the year for all 84 periods ( but of course I can vary the periods to be more or less in thde count tab before I trigger the macro) . Of course when 408 contacts becomes more, I can just use the formulae to count how many times to copy the contract list in the new version.
I have tried my best to get this to work, but I am quite the noob on macros. Any help would be most gratefully received ! the extra movements were to make sure the next copy was at the end of the existing range of copied data. Let me know if it is unclear what I am trying to do !
Code I have used so far is :
Sheets("COUNT").Select
Range("A380").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("DATA").Select
Range("A403").Select
Selection.End(xlUp).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
Range("A2").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Do Until Sheets("COUNT").Select.Range("l1") = Range("o1")
End Sub
TAB 1"Control" where the macro can be triggered,
TAB2 "Count" where all the contract ID's are dropped, in cell L1 the total number of lines of contracts are multiplied by all the months in a given time period. E.g for 2014-2020 by month there are 84 months, there are 408 contracts, so by adding 1 to this number, we get the number of rows including titles we require. In Cell o1 we have return back count of how many rows there are in the tab 3 "DATA" ( including the row titles) which is where i would like to copy the 408 contacts repeatedly , until I have the required number of lines to have a contract line for each month of the year for all 84 periods ( but of course I can vary the periods to be more or less in thde count tab before I trigger the macro) . Of course when 408 contacts becomes more, I can just use the formulae to count how many times to copy the contract list in the new version.
I have tried my best to get this to work, but I am quite the noob on macros. Any help would be most gratefully received ! the extra movements were to make sure the next copy was at the end of the existing range of copied data. Let me know if it is unclear what I am trying to do !
Code I have used so far is :
Sheets("COUNT").Select
Range("A380").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("DATA").Select
Range("A403").Select
Selection.End(xlUp).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
Range("A2").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Do Until Sheets("COUNT").Select.Range("l1") = Range("o1")
End Sub