Writing a Macro

[babykilers]

Dear all Excel Experts,
How are you and hope that you guys can help me on this.
In Sheet 1 - I have a table that will be in every day update ( number of rows will be different in each day ) .
- In the first column of table will be number 1,2,3,4,5... to the last row of table .

In Sheet 2 - I will do some calculation based on information founded in the table (Sheet 1 )
- In cell B2 will be criteria that look up the informations
- In cell B2 will be number from first column of table in Sheet 1
In sheet 3 - I will save data after calculation

I am trying to write a Macro to do the following:
1. Copy individually numbers from first column ( Sheet 1 ) beginning with first number (cell B11 ) to the end
2. Paste in cell B2 ( Sheet 2 )
After excel make all calculation ...
3. Run an macro ( I already wrote it ) named "Archive"
4. Repeat

 

[iggydarsa]

You will need to modify based on your need but this can give you a good start

Sub Test()
    'Copy Range from Sheet1 to Sheet2
    Worksheets("Sheet2").Range("B2:B" & Rows.Count).ClearContents
    lr = Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
    Worksheets("Sheet1").Range("B11:B" & lr).Copy Worksheets("Sheet2").Range("B2")
   
    'Run another macro
    Call Archive
End Sub

 

[babykilers]

You will need to modify based on your need but this can give you a good start

Sub Test()
    'Copy Range from Sheet1 to Sheet2
    Worksheets("Sheet2").Range("B2:B" & Rows.Count).ClearContents
    lr = Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
    Worksheets("Sheet1").Range("B11:B" & lr).Copy Worksheets("Sheet2").Range("B2")
  
    'Run another macro
    Call Archive
End Sub

This wiil work for copy and paste for entire column in one move but i want to copy each number from column an run macro after past and then copy next number and run again macro after past ... repeat to last number .

 

[iggydarsa]

Sub Test()
    'Copy Range from Sheet1 to Sheet2
    lr1 = Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
    lr2 = Worksheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
   
    For i = 11 To lr1
        Worksheets("Sheet2").Range("B" & lr2 + 1).Value = Worksheets("Sheet1").Range("B" & i).Value
         'Run another macro
         Call Archive
        lr2 = lr2 + 1
    Next i
  
    
End Sub