Macro for copying multiple cells from one worksheet to another

[rhysm144]

I’m trying to write a macro to copy results from one workbook to another rather than having to do it manually. I want to copy into my current worksheet (from STK.xlsm) the following cells:
Into Cell AB3 ='[STK.xlsm]Sheet1'!$AC$2
Into Cell AC3 ='[STK.xlsm]Sheet1'!$AC$16
Into Cell AD3 ='[STK.xlsm]Sheet1'!$AC$17
Into Cell AE3 ='[STK.xlsm]Sheet1'!$AD$8
Into Cell AF3 ='[STK.xlsm]Sheet1'!$AD$9
Into Cell AG3 ='[STK.xlsm]Sheet1'!$AD$10
Into Cell AH3 ='[STK.xlsm]Sheet1'!$AD$11
Into Cell AI3 ='[STK.xlsm]Sheet1'!$AC$5
Into Cell AJ3 ='[STK.xlsm]Sheet1'!$AC$3
Into Cell AK3 ='[STK.xlsm]Sheet1'!$AD$3

I want this to loop so the same cells in the next sheet (ie sheet2) are copied into the next row (AB4 etc) until the last sheet.

[rhysm144]

Crossposted at mrexcel: mrexcel.com/forum/excel-questions/971549-macro-copying-multiple-cells-one-worksheet-another.html#post4666429

[mancubus]

assumptions:

2 workbooks have the same number of sheets.
sheets' names are identical and start with "Sheet" and end with consecutive integers.
thus sheet names are Sheet1, Sheet2, Sheet3 ... SheetN

copy below code into a standard module in the workbook that you want to copy vlaues to.
open STK.xlsm
activate the macro workbook
run this macro

always test with backup files.

Sub vbax_57545_get_values_from_another_workbook()

    Dim sourceWB As Workbook
    Dim i As Long
    
    sourceWB = Workbooks("STK.xlsm")
    
    For i = 1 To Worksheets.Count
        Worksheets("Sheet" & i).Range("AB3").Value = sourceWB.Worksheets("Sheet" & i).Range("AC2").Value
        Worksheets("Sheet" & i).Range("AC3").Value = sourceWB.Worksheets("Sheet" & i).Range("AC16").Value
        Worksheets("Sheet" & i).Range("AD3").Value = sourceWB.Worksheets("Sheet" & i).Range("AC17").Value
        Worksheets("Sheet" & i).Range("AE3").Value = sourceWB.Worksheets("Sheet" & i).Range("AD8").Value
        Worksheets("Sheet" & i).Range("AF3").Value = sourceWB.Worksheets("Sheet" & i).Range("AD9").Value
        Worksheets("Sheet" & i).Range("AG3").Value = sourceWB.Worksheets("Sheet" & i).Range("AD10").Value
        Worksheets("Sheet" & i).Range("AH3").Value = sourceWB.Worksheets("Sheet" & i).Range("AD11").Value
        Worksheets("Sheet" & i).Range("AI3").Value = sourceWB.Worksheets("Sheet" & i).Range("AC5").Value
        Worksheets("Sheet" & i).Range("AJ3").Value = sourceWB.Worksheets("Sheet" & i).Range("AC3").Value
        Worksheets("Sheet" & i).Range("AK3").Value = sourceWB.Worksheets("Sheet" & i).Range("AD3").Value
    Next i

End Sub

[rhysm144]

Sorry for the super slow response.

Thank you for your help. Unfortunately I keep getting:

Run-time error '91':

Object variable or With block variable not set

with respect to "sourceWB = Workbooks("STK.xlsm")"

[rhysm144]

I believe I can get around this by using Set sourceWB etc.

More significant issue is your presumption that both workbooks have the same number of sheets and each sheet is logically subsequent i.e. Sheet1, Sheet2 etc

What I am attempting to do is take for example cell AC2 from the first sheet (not named sheet1) and copy it into cell AB3 (However if easier it could go into A1) in a different workbook or worksheet (whichever is easier). I then want cell AC2 on the second sheet (not named sheet2) copied into the row below e.g. A2.

I want this to occur for every sheet and have all the cells AC2,AC16,AC17,AD8,AD9,AD10,AD11,AC5,AC3,AD3 copied into a single row for every worksheet.

Sorry if this is still confusing.

[mancubus]

objects are assigned to variables using SET statement. sorry for that.

[mancubus]

so you have a destination sheet and you want all required cells' values (same cells for all sheets in the source workbook) get copied to the rows in this specific sheet.

try this, if i am not wrong.

Sub vbax_57545_get_values_from_another_workbook()
     
    Dim sourceWB As Workbook
    Dim destWS As Worksheet
    Dim i As Long
    
    Set sourceWB = Workbooks("STK.xlsm")
    Set destWS = ThisWorkbook.Worksheets("Sheet1") 'copies to workbook where this sub is located. change to suit
    'Set destWS = Workbooks("DestinationWBName").Worksheets("DestinationWSName") 'example
    
    
    'first iteration of i is 1. to start at row 3, add 2
    For i = 1 To sourceWB.Worksheets.Count
        destWS.Range("AB" & 2 + i).Value = sourceWB.Worksheets(i).Range("AC2").Value
        destWS.Range("AC" & 2 + i).Value = sourceWB.Worksheets(i).Range("AC16").Value
        destWS.Range("AD" & 2 + i).Value = sourceWB.Worksheets(i).Range("AC17").Value
        destWS.Range("AE" & 2 + i).Value = sourceWB.Worksheets(i).Range("AD8").Value
        destWS.Range("AF" & 2 + i).Value = sourceWB.Worksheets(i).Range("AD9").Value
        destWS.Range("AG" & 2 + i).Value = sourceWB.Worksheets(i).Range("AD10").Value
        destWS.Range("AH" & 2 + i).Value = sourceWB.Worksheets(i).Range("AD11").Value
        destWS.Range("AI" & 2 + i).Value = sourceWB.Worksheets(i).Range("AC5").Value
        destWS.Range("AJ" & 2 + i).Value = sourceWB.Worksheets(i).Range("AC3").Value
        destWS.Range("AK" & 2 + i).Value = sourceWB.Worksheets(i).Range("AD3").Value
    Next i
     
End Sub

if needed, sheet name can be added to column AA or AL or any other blank column.

[rhysm144]

Worked Perfectly.

Thank you

[mancubus]

you are welcome.
thanks for marking the thread as solved.