How to record data automatically

[kopsy]

Hi all,
I have dynamically changing A1:L1 row data in 5 different S1,S2,S3,S4,S5 worksheets, I wanted to copy and paste it in the next empty row continuously in the respective worksheet.

Please check the attachment file below and help me with the same.

Thank you.

 

[Marc L]

Hi,​

​

unclear so that needs a complete & crystal clear explanation or activate the Macro Recorder & operate manually as a starter …​

​

 

[kopsy]

Hi Marc,

There are 5 different worksheets named as S1,S2,S3,S4,S5, Each different worksheet has unique set of data ranged from A1:L1 (only one row), This data gets updating every 4-5minute basis from another Worksheet from the same workbook named as Import.

I need vba to copy A1:L1 every 5minutes and place it one row below at A2:L2 OR otherwise in a next blank row in the same sheet. ( this part i am able to succeed somehow in a single worksheet, but i'm not sure how to imply the same macro to work for the multiple worksheets. ) , below is the code working for the single worksheet for copying, pasting to the next blank row. please check,

Option Explicit

Dim myTimer As Date

Sub GetMyData()

Application.ScreenUpdating = False

Dim lastrow As Long, nextblankrow As Long

myTimer = Now + TimeValue("00:02:00")

Application.OnTime myTimer, "GetMyData"

Dim rng1 As Range

Set rng1 = Worksheets("S1").Range("A1:L1")

rng1.Copy

lastrow = Sheets("S1").Range("A" & Rows.Count).End(xlUp).Row

nextblankrow = lastrow + 1

Sheets("S1").Range("A" & nextblankrow).PasteSpecial xlPasteValues

ActiveWorkbook.RefreshAll

Application.ScreenUpdating = True

End Sub

Please make necessary changes in the code to work for copying and pasting to the next blank row in all the worksheets at the same time.

Also, check the attached file.

Thanks.

 

[Marc L]

As your attachments crash on my side on several computers, a demonstration as a starter to copy data after the refresh :​

Sub Demo1()
Dim R&
On Error Resume Next
ActiveWorkbook.RefreshAll
If Err.Number Then Beep: Exit Sub
On Error GoTo 0
For R = 1 To 5
Sheet1.UsedRange.Rows(R + 1).Copy Sheets("S" & R).Cells(Sheets("S" & R).UsedRange.Rows.Count + 1, 1)
Next
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[kopsy]

Hi Marc, thanks for responding.

please check the file below, i have removed the connection file from it, that might be the reason for the crash and errors, it may not crash now. please see to it.
thank you.

 

[Marc L]

As my demonstration well works on my side, did you try it at least ?​

​

You just need the loop part to copy data to each relevant worksheet …​

​

 

[kopsy]

I spent many hours to my little understand but it didn't work in the way i wanted. this code is grabbing the data from another sheet too, along with some formulas, it almost looks like shuffling, also i don't understand why it is seeing 1 to 5 row.

While copy and paste part, i don't want it to bring along with some formulas, it has to be like paste special only values.