Hi all!..
the concept of this spreadsheet is to have survey questions in one sheet and record the answers to the other with the PC name as a heading... no worries about the layout..i have it ready. all i want is automatically copy range from one spreadsheet to the other if blank..
Simply in words I want excel to look into one cell ..if it s blank paste special to it but if not paste to the next..or the next...and on..
i am doing many wrong things.. and i got stuck... i think some kind of loop statement and offset would do it... but i m not sure how to use it.. i m learning from ready made codes that i twist here and there.....now i m stuck
[VBA]Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets("survey").Range("a2:a52").Copy
If IsEmpty(Worksheets("score").Range("a2")) Then
Worksheets("score").Range("a2").PasteSpecial Paste:=xlPasteValues
Active.cell = user
Else
End If
Worksheets("survey").Range("a2").Select
End Sub[/VBA]
by active.cell = user .. i sort of wanted to write on top the name of the user(/PC) ..but i guess this doesn t do the trick?!?
Give this a try (sample attached)
[vba]
Option Explicit
Sub PartCopy()
Dim Rng1 As Range
Dim Tgt As Range
Dim i As Long
Set Rng1 = Worksheets("survey").Range("A2:A52")
Set Tgt = Worksheets("Score").Range("A1")
If Tgt = "" Then Tgt = Environ("Username")
For i = 1 To Rng1.Cells.Count
If Tgt.Offset(i) = "" Then
Tgt.Offset(i) = Rng1(i)
End If
Next
End Sub
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
good ..and pretty quick reply!!!! so, here is my attachment...
it was a template i have downloaded and some mods i tried to make to it!!
however yr code which i have included in the file ..does not go to the next column to fill next person's answers
ohh.. and i got a debug for variable i ..which i declared as integer.. shouldn t i?
bare in mind that the spsheet is initially empty! when the 1st person answers to the questionnaire.. it pastes say 10 data in column one.. when the 2nd person answers...it should find that column 1 is filled and proceed to column 2 to paste the same range... etc etc
Even simpler
[VBA]Option Explicit
Sub PartCopy()
Dim Rng1 As Range
Dim Tgt As Range
Set Rng1 = Worksheets("Survey").Range("A2:A52")
Set Tgt = Worksheets("Score").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
Tgt = Environ("Username")
Tgt.Offset(1).Resize(Rng1.Cells.Count).Value = Rng1.Value
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
awesome! while it s really simple..it does work indeed!!! would you mind if i ask the following:
-why do i not need the paste function? it is really short as a code but i do not really understand what it does tell vba to execute(although i know the end result!)
i had in mind that some kind of loop was the only way as well...which was a mistake since there is a shorter method
This line
[VBA]
Tgt.Offset(1).Resize(Rng1.Cells.Count).Value = Rng1.Value
[/VBA]
in effect says D1=A1, D2=A2, D3=A3 by assigning the value of each cel in the range to another cell. It is equivalent to Copy PasteSpecial/Values
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
If blank column, paste to the next column VBA
Reply With Quote
