Macro question

[tlee]

Hello all,

Could anyone tell me about using Macro /VBA as below

1) How to copy several whole columns from one sheet to another sheet within
the same Excel file?
2) How to detect the column and fill the number into another column?
e.g. ColumnA2 to A99 contains data, then it can fill the number from
1,2,3,4,5.....................,97,98 into the column B2 to B99
automatically?

Thank you for your help first.

TLee

 

[Patrick Molloy]

here are three examples for you:

1) copy a column on the same sheet
Range("J:J").Value = Range("E:E").Value

2)Copy a column to another sheet
Worksheets("sheet2").Range("J:J").Value = Range("E:E").Value

3) copy a range to another sheet
Dim source As Range
Set source = Worksheets("Sheet1").Range("E6:G20")
With source
Worksheets("sheet3").Range("J2").Resize(.Rows.Count,
..Columns.Count).Value = .Value
End With

 

[Bernie Deitrick]

TLee,

1)

Worksheets("Sheet1").Range("A:C").Copy Worksheets("Sheet2").Range("H:J")

2)

With Worksheets("Sheet1").Range("A2", Cells(Rows.Count, 1).End(xlUp)).Offset(0, 1)
.Formula = "=ROW()-1"
.Value = .Value
End With


HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Patrick,

?.Value = ?.Value is essentially paste special / values, not necessarily "copy"

Bernie

 

[JLGWhiz]

Try these:

Pastes columns c thru f of sheet one to columns a
thru d of sheet two.

Sub cpyExpl()
Sheets(1).Columns("C:F").Copy Sheets(2).Range("A1")
End Sub

move data using For Each loop.

Sub fe()
For Each c In Range("A2:A99")
If c.Value <> "" Then
c.Offset(0, 1) = c.Value
End If
Next
End Sub

 

[tlee]

Hi all,

Thank you all of yours help.

Bernie,
As for the point 2, how can I specified to detect the Column A only, which
contains data, and then write the sequence number (e.g. 1.........9999) into
the specified column G (start from G2) automatically?

Because, I found that all another columns data are also changed to the same
value as column G.

I change your code from "A2" to "G2"
With Worksheets("Sheet1").Range("G2", Cells(Rows.Count,
1).End(xlUp)).Offset(0, 1)
.Formula = "=ROW()-1"
.Value = .Value
End With

Thanks
Tlee

 

[Bernie Deitrick]

You also need to change the offset:

With Worksheets("Sheet1").Range("G2", Cells(Rows.Count,
1).End(xlUp)).Offset(0, 6)
.Formula = "=ROW()-1"
.Value = .Value
End With

HTH,
Bernie
MS Excel <VP

 

[tlee]

Bernie, JLGWhiz, Patrick,

Thanks alot for your helps

TLee