Macro question

  • Thread starter Thread starter tlee
  • Start date Start date
T

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
 
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
 
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
 
Patrick,

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

Bernie
 
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
 
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
 
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
 
Back
Top