Easy Excel Macro

[UTcse09]

I believe this should be easy for someone who actually knows macros.

I have two sheets in my workbook, and what I need to do is to copy certain columns from sheet1 to sheet2 for all rows in that column. The catch is I need to take the text being copied and add to it according to the column its being placed. The information in each row is different, but the text being added to it is predefined. For istance if I wanted to copy column F from sheet1 to column T of sheet2, and this is the result I wanted.
Sheet1 F1= Macro test
Sheet1 F2= Internet Test
Sheet2 T= Macro test -- Has passed
Sheet 2 T= Internet Test -- Has passed

Any and all help will be greatly appreciated.

P.S. this is on a windows xp machine running Office 2003.

 

[UTcse09]

This is what I have so far, but I know I need to reference the workbook somehow, and I need to add the addition text, but not sure how do either of those.



Sub Acopy()
Dim LR As Long, i As Long
LR = .Cell("A" & Rows.Count).End(xlUp).Row
.Rows(1).Copy Destination:=ThisWorkbook.Sheets("Sheet2").Cell("A1")
For i = 6 To LR
Range("T" & i).Text = Range("F" & i).Text
.Range("A" & i).Resize(, 1).Copy Destination:=ThisWorkbook.Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)

Next i
End Sub

 

[UTcse09]

So I gave up on that approach and I am now trying this, but I keep getting the same compiler error that says "Object Variable, or With Block Variable not set". My code is as follows.

Sub Acopy()
Dim LR As Long, i As Long
Dim OrigSheet As Sheet1
Dim NewSheet As Sheet2
Dim countnonblank As Integer, myRange As Range
Set myRange = Columns("A:A")
countnonblank = Application.WorksheetFunction.CountA(myRange)

For i = 6 To countnonblank
NewSheet.Range("T" & countnonblank).Value = OrigSheet.Range("F" & countnonblank).Value

Next i
End Sub


The error happens on the line inside my loop.

 

[tweedle]

Here's a snippet for one of your requests.
Hopefully you can adapt it to the other column.

Sub FCopy()
Dim C, rngUsed As Range
'modify as needed
Const strToAdd = " -- Has passed"
Const FirstRow = 2
'Turn off screen flicker
Application.ScreenUpdating = False
'Copy/Paste Column
Sheet1.Columns("F:F").Copy
Sheet2.Columns("T:T").PasteSpecial Paste:=xlPasteValues
LastRow = Sheet2.Range("T" & Rows.Count).End(xlUp).Row
Set rngUsed = Sheet2.Range(Sheet2.Cells(FirstRow, "T"), Sheet2.Cells(LastRow, "T"))
 
'For Each Cell in used cells of Col T (rngUsed)
For Each C In rngUsed
    'If not Empty, append const strToAdd
    If Not IsEmpty(C) Then
        C.FormulaR1C1 = C & strToAdd
    End If
Next

'Optional
'-------------------------
Sheet2.Columns("T:T").EntireColumn.AutoFit
Application.Goto Reference:=Sheet2.Range("T1")
'-------------------------
Application.ScreenUpdating = True
End Sub

 

[tweedle]

FYI your compiler error is due to the sheet references

Dim OrigSheet As Sheet1
Dim NewSheet As Sheet2

s/b
Dim OrigSheet, NewSheet As WorkSheet
Set OrigSheet = Sheet1
Set NewSheet = Sheet2