help with worksheets

[indraneel]

i have data in sheet one in one column and say many intermidiate rows

i want to copy that on a new sheet and then sort that


can anyone tell me how to do the linking using VBA


thanks in advance
indraneel

 

[Guest]

This does not 'Link' cells, it makes copy from source on currently selected
sheet and then pastes it on a second sheet (Sheet2 here) and then sorts it.

Sub MoveAndSort()
'presumes source data in column A
'starting in row 1 and continuing with
'no empty cells to the end of the list
Range("A1:" & Range("A1").End(xlDown).Address).Select
Selection.Copy
'actually 'move' to second sheet
Worksheets("Sheet2").Select 'use name of sheet to move to
Range("B1").Select ' where you wish to put it
ActiveSheet.Paste
'sort on single column, no header
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'or
'sort on single column, no header
' Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
' DataOption1:=xlSortNormal
End Sub

 

[indraneel]

yes i can do this but the part that i am interested in really is that i
move a cell to the next sheet with an "=" sign

i mean that the value is not copy pasted but linked by a formula to
sheet 1 cause i have many values that i have to take care of so was
planning to make a do loopwhile statement

regards
indraneel

 

[Guest]

Sorry, but now the system is not notifying me of replies.

What you need to do is set up a loop that works from start to finish on the
source sheet and creates an "=Sheet!x##" kind of formula to place in each of
the destination cells. Something like the following. The Sort should still
work.

Sub MakeLinks()
Dim LC As Long
Dim LastRowToLink As Long

'get on your first sheet
Worksheets("Sheet1").Select
'go to first cell to link to
Range("A1").Select
'determine last row to link
LastRowToLink = Range("A1").End(xlDown).Row
'or for last use if blanks in between
LastRowToLink = Range("A" & Rows.Count).End(xlUp).Row
Do Until ActiveCell.Offset(LC, 0).Row > LastRowToLink
'full reference to first destination on 2nd sheet
Worksheets("Sheet2").Range("B1").Offset(LC, 0).Formula = _
"=Sheet1!" & ActiveCell.Offset(LC, 0).Address
LC = LC + 1
Loop

End Sub
If you need more assistance: HelpFrom @ jlathamsite.com (no spaces).