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]

Need more information:

What are the names of the 3 different workbooks?
Where are the 3 workbooks located? All in the same folder or different locations?

What is the name of the worksheet that contains the pertinent data in each different workbook?

Please post the macro code you are using in workbook 2.

 

[wallstudio]

Name of Workbook (worksheet): 1. List (Analysis), 2. Calculator (Calculator), 3. Potential Stock (sheet1)
They are all in the same directory.

Actually, I need to input B3, B6, B7 of workbook 2 base on Column Q, D, E of workbook 1. Then I run 1st macro to get data from one of thousands of csv files for calculation. The result will be calculated by formulas in workbook 2. And I run a 2nd macro to paste results to workbook 3.

This is the 1st macro I use in workbook 2:

Sub GetData()

Application.ScreenUpdating = False

    Workbooks.Open Filename:=Range("V8") & Range("A4") & ".CSV", Local:=True
    
    ActiveSheet.Range(ActiveSheet.Range("A1").End(xlDown).Offset(-251, 0), ActiveSheet.Range("a1").End(xlDown).End(xlToRight)).Copy
    
    ThisWorkbook.Activate
    
        Sheets("Yearly Data").Select
        Range("B3").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        
        Sheets("Weekly").Select
    
        Sheets("Calculator").Select
    
    Windows(Range("A4") & ".CSV").Activate
    ActiveWindow.Close

End Sub

This is the 2nd macro I copy the result to workbook 3:

Sub PipeBtmTransfer()
Application.ScreenUpdating = False
    ThisWorkbook.Activate
    Range("A4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Potential Stock.xlsx").Activate
    Range("A1").End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    ThisWorkbook.Activate
    Range("B4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Potential Stock.xlsx").Activate
    Range("A1").End(xlDown).Offset(0, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    ThisWorkbook.Activate
    Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Potential Stock.xlsx").Activate
    Range("A1").End(xlDown).Offset(0, 2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    ThisWorkbook.Activate
    Range("B11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Potential Stock.xlsx").Activate
    Range("A1").End(xlDown).Offset(0, 3).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    ThisWorkbook.Activate
    Range("V9").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Potential Stock.xlsx").Activate
    Range("A1").End(xlDown).Offset(0, 4).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    ThisWorkbook.Activate
    Range("B8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Potential Stock.xlsx").Activate
    Range("A1").End(xlDown).Offset(0, 8).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    ThisWorkbook.Activate
    Range("J2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Potential Stock.xlsx").Activate
    Range("A1").End(xlDown).Offset(0, 9).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    ThisWorkbook.Activate
    Range("G8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B3").Select
    Windows("Potential Stock.xlsx").Activate
    Range("A1").End(xlDown).Offset(0, 10).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Application.CutCopyMode = False
    
    ActiveWorkbook.RefreshAll

End Sub

I hope you understand what I am trying to do. Thanks.

 

[Datsmart]

Actually, I need to input B3, B6, B7 of workbook 2 base on Column Q, D, E of workbook 1.

I don't understand what you are trying to do here:
Is this something you want to automate? If so, please explain in detail.

I made some changes to your posted macros to avoid "Selecting" and "Activating" when copying data.

Sub GetData2()
'Assign Workbook Variables
    VarW2 = ThisWorkbook.Name
    Sht2 = "Yearly Data"
'Open CSV file
    Workbooks.Open FileName:=Range("V8") & Range("A4") & ".CSV", Local:=True
'Copy Value to VarW2
    Workbooks(VarW2).Sheets(Sht2).Range("B3").Value = ActiveSheet.Range(ActiveSheet.Range("A1").End(xlDown).Offset(-251, 0).End(xlToRight)).Value
'   Close CSV file
    ActiveWindow.Close
End Sub

Sub PipeBtmTransfer2()
'Assign Workbook Variables
    VarW3 = "Potential Stock.xlsm"
    Sht3 = "Sheet1"
'Copy Values to VarW3:
    'Assign Variables for LastRows and LastColumns
    LR = Workbooks(VarW3).Sheets(Sht3).Range("A" & Workbooks(VarW3).Sheets(Sht3).Rows.Count).End(xlUp).Row + 1
    LC = 1
    Workbooks(VarW3).Sheets(Sht3).Cells(LR, LC).Value = Range("A4").Value
    LC = LC + 1
    Workbooks(VarW3).Sheets(Sht3).Cells(LR, LC).Value = Range("B4").Value
    LC = LC + 1
    Workbooks(VarW3).Sheets(Sht3).Cells(LR, LC).Value = Range("A1").Value
    LC = LC + 1
    Workbooks(VarW3).Sheets(Sht3).Cells(LR, LC).Value = Range("B11").Value
    LC = LC + 1
    Workbooks(VarW3).Sheets(Sht3).Cells(LR, LC).Value = Range("V9").Value
    LC = 9
    Workbooks(VarW3).Sheets(Sht3).Cells(LR, LC).Value = Range("B8").Value
    LC = LC + 1
    Workbooks(VarW3).Sheets(Sht3).Cells(LR, LC).Value = Range("J2").Value
    LC = LC + 1
    Workbooks(VarW3).Sheets(Sht3).Cells(LR, LC).Value = Range("G8").Value
    ActiveWorkbook.RefreshAll
End Sub

Code assumes they are run from your "Calculator" workbook.
I could not test everything as I do not have your CSV files and same data layout in my test files.

 

[wallstudio]

Thanks for the modification. Haven't test them yet, but I want to answer your question first.

What I have in workbook 1 are stock symbols in column Q, and Dates in column D and E. I need to input these three value to B3, B6, B7 of workbook 2. The macro GetData will base on value of B3 and open the corresponding csv which contain historical stock price. B6 and B7 are dates need for doing the calculation.

Therefore, each row of data set from workbook 1 after inputing to workbook 2 will be calculated a set of result which can be copy to workbook 3 for summary or comparison.

Hope these give you a more detail picture. Thanks a lot.

 

[Datsmart]

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"
    
'Cycle through each Row of VarW1 workbook
    For Rw = 2 To lr1
        Workbooks(VarW2).Sheets(Sht2a).Range("B3").Value = Workbooks(VarW1).Sheets(Sht1).Range("Q" & Rw).Value
        Workbooks(VarW2).Sheets(Sht2a).Range("B6").Value = Workbooks(VarW1).Sheets(Sht1).Range("D" & Rw).Value
        Workbooks(VarW2).Sheets(Sht2a).Range("B7").Value = Workbooks(VarW1).Sheets(Sht1).Range("E" & Rw).Value
        'Call Macro to open CSV file
        Call GetData2
        'Call Macro to copy values to VarW3
        Call PipeBtmTransfer2
    Next Rw
End Sub

This code assumes the three Workbooks, "List", "Calculator", and "Potential Stock" are all open. It also assumes the code is run from the "Calculator" workbook.
The code will loop through Rows 2 through the last row of data in Column Q in the "List" workbook. It will also call the Macros "GetData2" and "PipeBtmTransfer2" for each cycle of the loop.
I started the Loop on Row 2, adjust the code as needed if your data begins on a different row.
Note that my file extensions are different than just "xls", adjust the code to match your own files.

 

[wallstudio]

Thank you very much for your help. Macro "PipeBtmTransfer2" works perfect and your codes are really smart and simple. However, I get error on macro "GetData2" at

Workbooks(VarW2).Sheets(Sht2).Range("B3").Value = ActiveSheet.Range(ActiveSheet.Range("A1").End(xlDown).Offset(-251, 0).End(xlToRight)).Value

Also, for macro "GetData", I will get error when it open CSV file with less than 251 rows of data. How can I exit sub when it comes across this error.

 

[wallstudio]

Another thought. Don't know if I can grab whatever data of CSV file contains if there are not enough rows. That means if it contains 200 rows, it will grab all the 200 rows instead of exit sub. Thanks.

 

[Datsmart]

Finding a cell value by doing an offset of -251 rows is unusual.
Also, as you noted, it can fail if your file does not contain enough rows.

Can you explain what is in the CSV file that you are trying to locate and paste to cell B3?
There are more accurate ways to locate data than using OFFSET.

 

[wallstudio]

My CSV files are historical stock price data. Most of them will contain more than a year of data. I need 52 weeks of data for analysis where the last row represents the current date. And therefore I want to grab the last 251 rows of data.