CODEWITHSUNDEEP
excelvba
Leocodetwist
Leocodetwist

Reputation: 61

Display one column at a time

I would like to know if this is possible and how i could do this. I have a workbook with 2 sheets. Sheet2 has multiple columns with 50 different records. sheet1 i would like to have a play button or run button when i click "play/run" it it will have one column and that one column will display the records of each column from sheet2 until there are no more columns form sheet2. Additionally, the display will have a 5 sec interval before cycling to the next column.

i found out how to do the time interval but not the displaying 

'time interval 
Application.Wait Now + TimeSerial(0, 0, 5)

'displaying i have been using copy/paste but it does not work.

Thanks in advance

Upvotes: 0

Views: 197

Answers (2)

John Coleman
John Coleman

Reputation: 52008

A somewhat minimal-VBA approach:

Say that sheet2 looks like:

enter image description here

then in sheet1, create a 1-cell named range record (cell A2 in this case):

enter image description here

In column B put the formula: 

=IF(NOT(ISBLANK(INDIRECT("Sheet2!R"&ROW()&"C"&record,FALSE))),INDIRECT("Sheet2!R"&ROW()&"C"&record,FALSE),"")

and copy it down for as many rows as the longest record in sheet 2

Then -- the VBA part can just have a loop where it has the statement

Range("record").Value = i

(with an i that cycles through the column numbers containing records). The spreadsheet formula takes care of pulling the correct values.

Upvotes: 0

David Zemens
David Zemens

Reputation: 53653

Add these procedures to the VBA module. Then add a button or textbox/etc. to the Sheet1, and right-click the shape, and choose "Assign Macro", then select the MyButtonClick procedure. This will associate the macro with the button.

Then, you just need to loop over the columns and copy/paste like so:

Sub MyButtonClick()
Dim ws1 as Worksheet, ws2 as Worksheet
Dim cols as Range, c as Range

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

Set cols = ws2.Range("A1:G50")  '## Modify as needed

For Each c in cols.Columns

    c.Copy Destination:=ws1.Range("A1")  '## Puts the column in Sheet1, Column A
    Call WaitForIt(5)
Next

End Sub

Sub WaitForIt(seconds as Long)

    Application.Wait Now + TimeSerial(0, 0, seconds)

End Sub

Upvotes: 1

Related Questions