Help in Excel macro [Closed]

Report
-
 Danny -
Hi,

I have a work sheet with probably say 7 columns and each of them has a index name for example

Speed time distance
120 2 19
135 4 35
119 3 45

iam trying to search say speed and select that column and paste it in another worksheet named "speedvstime and speedvsdistance similarly iam trying to time and distances in the respective worksheet to.

this is my code so far...it is letting me select speed column and paste it in both worksheet
but when i try to do the same for say time. nothing happens

please help..thanks in advance

Sub counter()

Dim K, Co, i,j As Integer 'Initializing variables
Dim WorksheetName, Heading As String

K = Range("A65536").End(xlUp).Row 'finding the last row before blank
Co = Range("A1").End(xlToRight).Column 'Finding the last Column before blank

For i = 1 To Co
Heading = Cells(2, i) 'Store the Cell Value
If UCase(Heading) = UCase("Steering angle") Then 'Check the Value with the User given column name

'Select and Copy
Cells(2, i).Select
Range(Selection, Selection.End(xlDown)).Select 'Select the Column that has Steering angles
Selection.Copy

'Select and Paste
Sheets("SteeringVsSpeed").Select 'Open Worksheets with steering channels and pasting
Range("B1").Select
ActiveSheet.Paste
Sheets("SteeringVsLateral G").Select
Range("B1").Select
ActiveSheet.Paste
End If
Next i

For j=1 to Co
Heading = Cells(2, j) 'Store the Cell Value
If UCase(Heading) = UCase("Speed") Then 'Check the Value with the User given column name

'Select and Copy
Cells(2, j).Select
Range(Selection, Selection.End(xlDown)).Select 'Select the Column that has Speed
Selection.Copy

'Select and Paste
Sheets("SteeringVsSpeed").Select 'Open Worksheets with Speed channels and pasting
Range("A1").Select
ActiveSheet.Paste
Sheets("TimeVsSpeed").Select
Range("A1").Select
ActiveSheet.Paste
End If
Next i

End Sub

1 reply

You might want to try assigning the sheet names to the variables. If you work between sheets, Excel will get
"confused" if you just say....for example, Range("A1"). It is fine in the active sheet, but once you start moving
between sheets, then Range("A1") of what sheet?

For example:

Instead of,
K = Range("A65536").End(xlUp).Row 'finding the last row before blank
Co = Range("A1").End(xlToRight).Column 'Finding the last Column before blank

Try:

K= Sheets("MySheet").Range("A65536").End(xlUp).Row
Co = Sheets("MySheet").Range("A1").End(xlToRight).Column
Thanks,

It worked...I was not activating the worksheet

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!