Databases
StackOverflow2013
Postsdbo
POUsing Timer to coincide with Time, using PasteSpecial Only values and copy data row to row plus one
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing Timer to coincide with Time, using PasteSpecial Only values and copy data row to row plus one
    primarykey
    data
    text
    <p>@simoco has been very helpful(basically, gave the whole code)</p> <p>I now want to make some changes which I need a little tweaking, hope everyone can help:</p> <p>Code:</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' 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 Sub Activate_timer() Application.OnTime Now + TimeValue("00:60:00"), "MoveData" End Sub 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 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>Edit: Getting a debug error in MoveData, code which gets highlighted: mainworkBook.Sheets(sheetName.Value).Range("A" &amp; currentRow.Value).PasteSpecial xlPasteValues</p> <p>I have added a new sheet and renamed it "params", The error: Run-time error '9': Subscript out of Range</p> <p>Thanks.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSid
    1. USSid
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUsing Timer to coincide with Time, using PasteSpecial Only values and copy data row to row plus one
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POUsing Timer to coincide with Time, using PasteSpecial Only values and copy data row to row plus one
      1. This table or related slice is empty.
      1. VTDownMod
    3. VO
      singulars
      1. POUsing Timer to coincide with Time, using PasteSpecial Only values and copy data row to row plus one
      1. This table or related slice is empty.
      1. VTUpMod
    1. COReading tutorials is helpful and you should do it. We're here to answer questions which have not been answered before. We're not here to do your programming work.
      singulars
      1. POUsing Timer to coincide with Time, using PasteSpecial Only values and copy data row to row plus one
      1. USThomas Weller
    2. CO@ThomasW.Thanks for your input, I am doing the tutorials(MSDN one for VBA at the moment).
      singulars
      1. POUsing Timer to coincide with Time, using PasteSpecial Only values and copy data row to row plus one
      1. USSid
    3. COUsing the VBA Object Browser (F2) and Help (F1) are great resources when learning how to program. Have you tried searching PasteSpecial in the Object Browser?
      singulars
      1. POUsing Timer to coincide with Time, using PasteSpecial Only values and copy data row to row plus one
      1. USMark Fitzgerald
 

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