VBA conditional paste

[justme1122]

Hi Im a noob at VBA and have been trying to working this out for a little while now.

I have several template sheets that update data from csv file. Some of these templates are huge files due to formulas and to minimise file size I delete all but the top line of the formulars before saving or emailing and then copy and paste them back in when I open the file. Most of these files will have a diffrent number of rows.

So below is basic VBA but what I need it to paste in colum b if only there is data in colum A. Data is sorted so you could select the paste range by counting the rows in colum A.

Sub Macro1()
 
' Macro1 Macro
'
'
    Range("b1").Select
    Selection.Copy
    Range("b3").Select
    ActiveSheet.Paste
    Range("A1").Select
End Sub

Thanks so much for any help

 

[cb12]

Not at a copy of Excel to test this so hopefully someone will come along and hel pyou further. Assuming you are looking to copy information from column A to column B if A is not blank.

You can copy from A to B something like this

Range("A1").Copy Destination:= Range("B1")

To loop you could try something like this - I will test this when I am home later if no one else comes up with a workable solution.

[COLOR=black][FONT=Verdana]Sub copy_data()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim i, j, k As Integer<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Range("A65536").Select
Selection.End(xlUp).Select
i = activecell.row<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p> </o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Range("A2").Select<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]For j = 1 To i<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]k = activecell.row<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]If Range("A" & k).Value <> Empty Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Range("B" & k).Value = Range("A" & k).Value<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]k = k + 1<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Else k = k + 1<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Next j<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub<o:p></o:p>[/FONT][/COLOR]

Something along those lines should work anyway assuming your first line of data to copy is in A2. Hopefully this wil lget you on your way until someone comes along with a better idea that they can test.

 

[justme1122]

Thanks so much, your code came up with:

compile error:
Next without for

I was how ever able to work in the line into an existing code that have been using:

The following lists the names of worksheets in (B + i) and pastes A1 into cell (A + i).

Sub BuildIndex_Click()
Dim i As Long
For i = 1 To Worksheets.Count
    [COLOR=yellowgreen]Range("A1").Copy Destination:=Range("A" & i + 5)[/COLOR]
    Range("B" & i + 5) = Sheets(i).Name
Next
End Sub
Private Sub ClearIndex_Click()
Dim response
Dim i As Long
response = MsgBox(prompt:="ok to clear Index or cancel", Buttons:=vbOKCancel)
If response < 2 Then
For i = 1 To Worksheets.Count + 6
    Range("A" & i + 5) = ""
    Range("B" & i + 5) = ""
    Next
End If
End Sub

Thanks again, you'll laugh at me but I'v spent hours trying to work out how to do this.

 

[AlphaFrog]

Shortened code for ClearIndex_Click for what it's worth.

Private Sub ClearIndex_Click()

    If MsgBox(prompt:="OK to clear Index or Cancel", Buttons:=vbOKCancel) = vbOK Then

        Range("A6:B6").Resize(Worksheets.Count + 6).ClearContents

    End If
    
End Sub