PDA

View Full Version : If blank column, paste to the next column VBA



tsatsos007
03-18-2011, 06:23 AM
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 :banghead:

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

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?!?

mdmackillop
03-18-2011, 06:34 AM
Welcome to VBAX

Give this a try (sample attached)

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

tsatsos007
03-18-2011, 06:45 AM
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

got the idea?

mdmackillop
03-18-2011, 09:53 AM
Even simpler
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

tsatsos007
03-21-2011, 03:54 AM
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

can u please explain in short ?

mdmackillop
03-21-2011, 05:39 AM
This line

Tgt.Offset(1).Resize(Rng1.Cells.Count).Value = Rng1.Value


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