Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Here is a code. I've tried to comment it in details, but if you have some questions, please, ask.</p> <p>Public variables</p> <pre><code>Public currentRow As Range ' range with number of row in which the value should be inserted' Public sheetName As Range 'range with sheet name in which the value should be inserted' Public mainworkBook As Workbook Public stopTimer As Boolean ' variable to indicate whether to call timer again or not' </code></pre> <p>Init sub (you should call it in the ribbon to get macro work)</p> <pre><code>Sub Initiate() Set mainworkBook = ThisWorkbook ' the first line where you want to insert the value' Set currentRow = mainworkBook.Sheets("params").Range("a1") 'name of the sheet in wich we will paste data' Set sheetName = mainworkBook.Sheets("params").Range("b1") stopTimer = False Call MoveData End Sub </code></pre> <p>Timer:</p> <pre><code>Sub Activate_timer() Application.OnTime Now + TimeValue("00:05:00"), "MoveData" End Sub </code></pre> <p>MoveData:</p> <pre><code>Sub MoveData() mainworkBook.Sheets("Sheet2").Rows(3).EntireRow.Copy mainworkBook.Sheets(sheetName.Value).Range("A" &amp; currentRow.Value).PasteSpecial xlPasteValues 'increment the counter so the next time insert values to an empty string' currentRow.Value = currentRow.Value + 1 'if all rows are filled' If (currentRow.Value = Rows.Count + 1) Then Call ChangeSheet End If If (Not stopTimer) Then Call Activate_timer End If End Sub </code></pre> <p>If we fill all rows in the sheet:</p> <pre><code>Sub ChangeSheet() Dim currentSheet As Range Set currentSheet = sheetName 'determine what the next sheet name should be (you can add your own mapping)' Select Case currentSheet.Value Case "Sheet1": sheetName.Value = "Sheet3" Case "Sheet3": sheetName.Value = "Sheet4" Case "Sheet4": sheetName.Value = "Sheet5" Case Else: MsgBox "There is no sheets any more" End Select 'if we didn't change sheet' If (currentSheet.Value = sheetName.Value) Then stopTimer = True Exit Sub End If ' copy and paste headers (where A1:F1 - is a range of headers, you should write actual address)' mainworkBook.Sheets(currentSheet.Value).Range("A1:F1").Copy mainworkBook.Sheets(sheetName.Value).Range("A1").PasteSpecial xlPasteValues 'copy last row to the new sheet mainworkBook.Sheets(currentSheet.Value).Rows(Rows.Count).EntireRow.Copy mainworkBook.Sheets(sheetName.Value).Range("A2").PasteSpecial xlPasteValues ' make new value for the current row on the new sheet (where we will paste new values)' currentRow.Value = 3 End Sub </code></pre> <p>Hope it works:)</p>
    singulars
    1. This table or related slice is empty.
    1. POUsing Timer to coincide with Time, using PasteSpecial Only values and copy data row to row plus one
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USDmitry Pavliv
    1. USDmitry Pavliv
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POUsing Timer to coincide with Time, using PasteSpecial Only values and copy data row to row plus one
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks, Works perfectly.
      singulars
      1. PO
      1. USSid
    2. COis it possible to remove the timer and set-it up in such a way as to input the value as and when the row has changes? Working through the VB course over at MSDN is it the right way to go or? Thanks :)
      singulars
      1. PO
      1. USSid
    3. COOh and I just noticed that when the worksheet is closed and re-opened it starts pasting the values as per the reference given in the code and not where it left off. Guess I will change it every time before hitting initiate.
      singulars
      1. PO
      1. USSid
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload