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

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.Coun t,
..Columns.Count).Value = .Value
End With

"tlee" wrote in message
...
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

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


"tlee" wrote in message ...
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,

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

Bernie


"Patrick Molloy" wrote in message
...
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.Coun t, .Columns.Count).Value = .Value
End With

"tlee" wrote in message ...
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

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" wrote in message
...
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

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


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


"tlee" wrote in message
...
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

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" wrote in message
...
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


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


"tlee" wrote in message
...
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

Bernie, JLGWhiz, Patrick,

Thanks alot for your helps

TLee


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" wrote in message
...
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


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


"tlee" wrote in message
...
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