PDA

View Full Version : [SOLVED] Macro for copying multiple cells from one worksheet to another



rhysm144
10-25-2016, 04:32 PM
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
10-25-2016, 04:35 PM
Crossposted at mrexcel: mrexcel.com/forum/excel-questions/971549-macro-copying-multiple-cells-one-worksheet-another.html#post4666429

mancubus
10-28-2016, 01:59 AM
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
11-10-2016, 04:57 PM
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
11-10-2016, 05:08 PM
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
11-11-2016, 01:52 AM
objects are assigned to variables using SET statement. sorry for that.

mancubus
11-11-2016, 02:00 AM
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
11-11-2016, 11:19 AM
Worked Perfectly.

Thank you

mancubus
11-12-2016, 12:58 AM
you are welcome.
thanks for marking the thread as solved.