Macro to copy cell one after another from another workbook

[wallstudio]

Hi, I have workbook 1 with source data in a column. Workbook 2 is a calculator to get result from data in workbook 1 by running a macro. Workbook 3 is to collect all the results calculated by workbook 2 and put them in a column.

Therefore, I need to copy the source data one at a time to workbook 2. Run macro in workbook 2. Then copy the calculated cell to workbook 3. These steps need to be repeated until the end of the source column in workbook 1. Finally, workbook 3 should have all the calculated results in a column.

I know how to do copies. but don't know how to do one after another in the macro.

Can anyone give me some clue to link all those steps together? Thanks a lot.

 

[Datsmart]

I can put together some code to copy the last 251 rows of data, or if there is not that many rows available, fewer rows.
I would need to know what columns you want copied and which would be the first row of that range when there are less than 251 rows.

 

[wallstudio]

Thanks for your help. My CSV files have 6 columns from A to F with dates in column A in ascending order. Row 1 is the heading, which should not be copied. Therefore if there are fewer rows, the starting row to be copied should be row 2.

 

[Datsmart]

Sub GetData2()
'Assign Workbook Variables
    VarW2 = ThisWorkbook.Name
    Sht2 = "Yearly Data"
'Open CSV file
    Workbooks.Open FileName:=Range("V8") & Range("A4") & ".CSV", Local:=True
'Assign TopRow and LastRow variables
    lrc = Range("A" & Rows.Count).End(xlUp).Row
    If lrc > 252 Then
        tr = lrc - 251
    Else
        tr = 2
    End If
'Copy Data to VarW2
    ActiveSheet.Range(Cells(tr, "A"), Cells(lrc, "F")).Copy Workbooks(VarW2).Sheets(Sht2).Range("B3")
'Close CSV file
    ActiveWindow.Close
End Sub

This code will copy the last 251 rows of columns A - F, or fewer rows if less than 252 rows, to the Calculator Workbook.

 

[wallstudio]

This is perfect. You are so helpful. Thank you very much, John.

 

[wallstudio]

I got another question regarding the macro "GetData1"

lr1 = Workbooks(VarW1).Sheets(Sht1).Range("B" & Workbooks(VarW1).Sheets(Sht1).Rows.Count).End(xlUp).Row

Can you modify it to consider only the visible rows. My data is in a table with filter on.

Thank you.

 

[Datsmart]

Changing the "LastRow" number variable will not affect copying just the visible rows.
Need to add some code to just work with visible rows. Below changed code should do what you are asking.

Sub GetData1()
'Assign Workbook Variables
    VarW1 = "List.xlsx"
    Sht1 = "Analysis"
    lr1 = Workbooks(VarW1).Sheets(Sht1).Range("Q" & Workbooks(VarW1).Sheets(Sht1).Rows.Count).End(xlUp).Row
    VarW2 = ThisWorkbook.Name
    Sht2a = "Calculator"
'Assign Range to only visible rows
    Set Rng = Workbooks(VarW1).Sheets(Sht1).Range("Q2:Q" & lr1).SpecialCells(xlCellTypeVisible)
'Cycle through each Row of VarW1 workbook
    For Each c In Rng
        Workbooks(VarW2).Sheets(Sht2a).Range("B3").Value = Workbooks(VarW1).Sheets(Sht1).Range("Q" & c.Row).Value
        Workbooks(VarW2).Sheets(Sht2a).Range("B6").Value = Workbooks(VarW1).Sheets(Sht1).Range("D" & c.Row).Value
        Workbooks(VarW2).Sheets(Sht2a).Range("B7").Value = Workbooks(VarW1).Sheets(Sht1).Range("E" & c.Row).Value
        'Call Macro to open CSV file
        Call GetData2
        'Call Macro to copy values to VarW3
        Call PipeBtmTransfer2
    Next c
End Sub

 

[wallstudio]

Thank you once again. However, I got error here.

Set Rng = Workbooks(VarW1).Sheets(Sht1).Range("Q2:Q" & lr1).SpecialCells(xlCellTypeVisible)

 

[wallstudio]

I think I get it fixed. The table header is on row2. After I change it to row3, it works perfect. Thank you very much. You are so great.