-2

The task is a series of copy and pastes from worksheet A to worksheet B and then one copy of a newly calculated cell from worksheet B back into worksheet A. Think of the 3 values from worksheet A as being model inputs and worksheet B is the model. Once they are entered the model creates the output value which I then want to place nest to the corresponding row of inputs in worksheet A.

  • Cell G2 in worksheet A is numeric and must go to cell G25 in worksheet B.
  • Cell H2 in worksheet A is also numeric and must go to cell G30 in worksheet B.
  • Cell C2 in worksheet A is text and must be pasted (paste special’d) into cell C5 in worksheet B.
  • Finally the output variable (cell D3) from worksheet B must be pasted back into cell I2 in worksheet A.

The number of rows in worksheet A happens to be 4,040 (rows 2 to 4,041) in this case but can be any number going forward.

Here is mu code currently:

Sub CopyPasteLoop3()
    
    'note: Long refers to a datatype for potentially long numbers
    Dim i As Long
    Dim wb As Workbook
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet

    Set wb = ThisWorkbook
    Set sht1 = wb.Sheets("Sheet13")
    Set sht2 = wb.Sheets("Sheet2")


    'loop from Row2 to last row with a value in Column G
    For i = 2 To sht1.Cells(Rows.Count, "G").End(x1Up).Row
    'assign the 3 inputs
        sht2.Range("G25").Value = sht1.Cells(i, "G").Value
        sht2.Range("G30").Value = sht1.Cells(i, "H").Value
        sht2.Range("C5").Value = sht1.Cells(i, "C").Value
        sht2.Calculate
    'copy the output
        sht1.Cells(i, "I").Value = sht2.Range("D3").Value

    Next i
End Sub
Improve this question
4
  • 1
    "output variable (cell H10) from worksheet B must be pasted back into cell D3 in worksheet A" - not D2 (same row as the inputs) ? Commented Oct 7 at 17:23
  • Tim, I meant to say D2 (back into the same row as the input variables). thanks Commented Oct 7 at 20:06
  • FYI in your revised code End(x1Up) should be End(xlUp) Commented Oct 8 at 19:38
  • There is no question in your question. Did you forget something? Commented Oct 8 at 20:15

1 Answer 1

Reset to default
4

Try this out:

Option Explicit   '<<<< should be at the top of every code module

Sub Tester()
    Dim i As Long, calcMode
    Dim wb As Workbook, sht1 As Worksheet, sht2 As Worksheet

    Set wb = ThisWorkbook            'If your data is in the same workbook as this code
             'ActiveWorkbook         'If you want to act on the workbook which has focus
             'Workbooks("blah.xlsx") 'If you want to operate on a specific named workbook

    Set sht1 = wb.Sheets("Sheet1") 'Assign sheets by tab names
    Set sht2 = wb.Sheets("Sheet2")   
    
    'optional: improve performance
    calcMode = Application.Calculation 'save current setting
    Application.Calculation = xlCalculationManual 'set to manual
    
    For i = 2 To sht1.Cells(Rows.Count, "A").End(xlUp).Row
        sht2.Range("E7").Value = sht1.Cells(i, "A").Value
        sht2.Range("F8").Value = sht1.Cells(i, "B").Value
        sht2.Range("G9").Value = sht1.Cells(i, "C").Value
        sht2.Calculate
        sht1.Cells(i, "D").Value = sht2.Range("H10").Value
    Next i
    
    Application.Calculation = calcMode 'restore calc setting if you changed it
    
End Sub

Some comments added to clarify ThisWorkbook vs ActiveWorkbook etc. Note that ThisWorkbook and ActiveWorkbook are the same if the workbook with the code happens to be the one with focus.

Improve this answer
Sign up to request clarification or add additional context in comments.

17 Comments

Thank you. I ran it and was given an error I am not familiar with. It was "Run-time error '9': Subscript out of range" When I clicked to de-bug it highlighted this line: Set sht2 = wb.Sheets("Sheet2") and Sheet2 does in fact exist
I am assuming that ThisWorkbook simply refers to whatever workbook I have open and am running the macro in and isn't where I am supposed to put in the actual name of the workbook, right?
To be clear: ThisWorkbook is the workbook that contains the VBA code, not whatever you have open and active. So if your code is in Personal.XLSB, it won't work. Try ActiveWorkbook instead.
Yes - ThisWorkbook is the workbook where the VBA code is running.
thanks. Do you know why I may be getting the "subscript out of range" error when the sheet i am referencing (Sheet2) IS in fact in the workbook?
If the error occurs on Set sht2 = wb.Sheets("Sheet2") then that's telling you there's no worksheet with that name in the workbook where the code is running. Make sure you're looking at the Tab name and not the worksheet code names - they're not always the same. Eg. see stackoverflow.com/questions/41477794/…
it looks like what I have called Sheet2 is Sheet2 when I look at the codename
Are you using Option Explicit at the top of your code module? If not then add it and see if anything changes. I notice your original post has "smart quotes" in place of regular quotes - VBA doesn't see smart quote as quotes, so if you use those the whole quoted string ends up as a variable named (eg) “Sheet2”
1. It looks like I have the "regular quotes" in my current code now (I'll share the code after this) 2. When I try to add Option Explicit it seems to only allow it before the Sub Tester() line up top, which has a line between it and the start of the Subroutine (makes me wonder if it's actually not included and is instead the final line in the Subroutine above it). 3. I'm still getting the 'subscript out of range' error, but now it is pointing toward the other sheet (sht1). I just changed both code names to match the sheet names (in this case they are Sheet13 and Sheet2) and th
If you have substantial comments (and more code) then you should edit your question to add it there. Option Explicit goes at the very top of the module - it is a general directive to VBA to make sure all variables are declared (to save you from typos and smart quotes)
Thanks. I edited my Question to reflect the latest code (and updated the cells with the actual ones I am using).
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.