Copying lots of cells values between sheets

[glad_ir]

Hello All,

I wonder if anybody can help me with this one please? I need to copy lots cells from one sheet to another sheet in the same workbook. The cells being copied from/pasted to aren't arranged in the same format....but the copy/paste pattern does repeat - e.g. below

Cells to be copied in sheet 1	Cells to be pasted to in sheet 2
A12	B2
A14	E2
A16	C2
A18	D2
A20	F2

etc to A40.....

then repeat the same pattern for sheet1 AA12 being copied across to sheet2 AB2..........then BA12 copied to sheet 2 BB2 etc......and on and on.

Is there a way to do this manually with a macro to save my sanity?

Any help is much appreciated.

Thanks, Iain

 

[JLGWhiz]

Excel does not recognize 'etc.' nor 'on and on' as parameters for vba code nor formulas. If you want reliable suggestions then you need to provide the ranges to copy and the ranges to paste to with clear parameters. eg. Copy Beginning with cell A12 and every even numbered cell to the end of the data in Column A. Paste to Row 2 of Sheet 2 in the column order B, E, C, D, F,, G (list the complete order to match the number of copied cells). If you cannot describe your project in words, then make mock up copies of your worksheets and post screen shots of the source sheet and a sheet showing the expected results. or provide a link to your files on a share server.

 

[glad_ir]

Hi, thank you for replying and sorry for not being clearer. Hopefully below is a better explanation.

I have a workbook containing 2 worksheets. Worksheet 1 contains 30 series each containing 16 data sets. Each data set contains 42 cells that need to be copied across into a row in worksheet 2 (ideally with 0 reflected as "" in worksheet 2). Below are origin and destination cells for the first 4 data sets in series 1

Series 1 data set 1

Cell to be copied in sheet 1​	Cell to be pasted to in sheet 2​
D28​	C7​
F44​	G7​
J20​	H7​
G52​	I7​
F54​	J7​
J23​	L7​
J42​	M7​
J36​	N7​
M22​	P7​
F60​	AM7​
G60​	AN7​
H60​	AO7​
I60​	AP7​
F61​	AQ7​
G61​	AR7​
H61​	AS7​
I61​	AT7​
F62​	AU7​
G62​	AV7​
H62​	AW7​
I62​	AX7​
F63​	AY7​
G63​	AZ7​
H63​	BA7​
I63​	BB7​
F64​	BC7​
G64​	BD7​
H64​	BE7​
I64​	BF7​
F65​	BG7​
G65​	BH7​
H65​	BI7​
I65​	BJ7​
F66​	BK7​
G66​	BL7​
H66​	BM7​
I66​	BN7​
F67​	B07​
G67​	BP7​
H67​	BQ7​
I67​	BR7​
H68​	BS7​

Series 1 data set 2

Cell to be copied in sheet 1	Cell to be pasted to in sheet 2​
R28​	C8​
S44​	G8​
W20​	H8​
T52​	I8​
S54​	J8​
W23​	L8​
W42​	M8​
W36​	N8​
Z22​	P8​
S60​	AM8​
T60​	AN8​
U60​	AO8​
V60​	AP8​
S61​	AQ8​
T61​	AR8​
U61​	AS8​
V61​	AT8​
S62​	AU8​
T62​	AV87​
U62​	AW8​
V62​	AX8​
S63​	AY8​
T63​	AZ8​
U63​	BA8​
V63​	BB8​
S64​	BC8​
T64​	BD8​
U64​	BE8​
V64​	BF8​
S65​	BG8​
T65​	BH8​
U65​	BI8​
V65​	BJ8​
S66​	BK8​
T66​	BL8​
U66​	BM8​
V66​	BN8​
S67​	B08​
T67​	BP8​
U67​	BQ8​
V67​	BR8​
U68​	BS8​

Series 1 data set 3

Cell to be copied in sheet 1	Cell to be pasted to in sheet 2​
AD28​	C9​
AF44​	G9​
AJ20​	H9​
AG52​	I9​
AF54​	J9​
AJ23​	L9​
AJ42​	M9​
AJ36​	N9​
AM22​	P9​
AF60​	AM9​
AG60​	AN9​
AH60​	AO9​
AI60​	AP9​
AF61​	AQ9​
AG61​	AR9​
AH61​	AS9​
AI61​	AT9​
AF62​	AU9​
AG62​	AV9​
AH62​	AW9​
AI62​	AX9​
AF63​	AY9​
AG63​	AZ9​
AH63​	BA9​
AI63​	BB9​
AF64​	BC9​
AG64​	BD9​
AH64​	BE9​
AI64​	BF9​
AF65​	BG9​
AG65​	BH9​
AH65​	BI9​
AI65​	BJ9​
AF66​	BK9​
AG66​	BL9​
AH66​	BM9​
AI66​	BN9​
AF67​	B09​
AG67​	BP9​
AH67​	BQ9​
AI67​	BR9​
AH68​	BS9​

Series 1 data set 4

Cell to be copied in sheet 1	Cell to be pasted to in sheet 2​
AR28​	C10​
AS44​	G10​
AW20​	H10​
AT52​	I10​
AS54​	J10​
AW23​	L10​
AW42​	M10​
AW36​	N10​
AZ22​	P10​
AS60​	AM10​
AT60​	AN10​
AU60​	AO10​
AV60​	AP10​
AS61​	AQ10​
AT61​	AR10​
AU61​	AS10​
AV61​	AT10​
AS62​	AU10​
AT62​	AV10​
AU62​	AW10​
AV62​	AX10​
AS63​	AY10​
AT63​	AZ10​
AU63​	BA10​
AV63​	BB10​
AS64​	BC10​
AT64​	BD10​
AU64​	BE10​
AV64​	BF10​
AS65​	BG10​
AT65​	BH10​
AU65​	BI10​
AV65​	BJ10​
AS66​	BK10​
AT66​	BL10​
AU66​	BM10​
AV66​	BN10​
AS67​	B010​
AT67​	BP10​
AU67​	BQ10​
AV67​	BR10​
AU68​	BS10​

Series 1 data set 5 – start with BD28 in sheet 1 being copied to C11 in sheet 2
Series 1 data set 6 – start with BR28 in sheet 1 being copied to C12 in sheet 2
Continue to Series 1 data set 16 – start with GR28 in sheet 1 being copied to C22 in sheet 2

then move to row 2.......

Series 2 data set 1 - start with D93 in sheet 1 being copied to C26 in sheet 2
Series 2 data set 2 - start with R93 in sheet 1 being copied to C27 in sheet 2
Series 2 data set 3 - start with AD93 in sheet 1 being copied to C28 in sheet 2
Series 2 data set 4 - start with AR93 in sheet 1 being copied to C28 in sheet 2
Continue to Row 2 data set 16.......

I have 30 series in total like this!!

Hoping for a way to do all or even part of this with a macro.

Any help you can offer would be much appreciated.

thanks,
Iain

 

[JLGWhiz]

Thank you for the expanded example of the copy and paste ranges. Unforunately, the pattern of the data is too erratic for efficient coding with vba. For example data set 1, series 1 uses a span of 9 columns, while data set 2, series 1 uses a span of 10 columns. Trying to set up code to sync those differences could be a nightmare. I had thought that I could use a couple of arrays with nested loops to pick the source data out and paste it to the proper location on sheet 2, but the inconsistency between data sets will not fit that scenario. sorry. Maybe someone else will take a stab at it.
Regards, JLG

 

[glad_ir]

Thank you for the expanded example of the copy and paste ranges. Unforunately, the pattern of the data is too erratic for efficient coding with vba. For example data set 1, series 1 uses a span of 9 columns, while data set 2, series 1 uses a span of 10 columns. Trying to set up code to sync those differences could be a nightmare. I had thought that I could use a couple of arrays with nested loops to pick the source data out and paste it to the proper location on sheet 2, but the inconsistency between data sets will not fit that scenario. sorry. Maybe someone else will take a stab at it.
Regards, JLG

Hi JLG, Thank you for taking a look. I thought that might be the case (with very limited experience I hasten to add!).

Do you think there's anything I can do with TRANSPOSE for the bottom part of each data set which is more ordered (F60 onwards in the 1st data set) to convert a 4 column x 8 row table into a single row of data? This would be a massive help but I don't know whether TRANSPOSE (or anything else) can do this?

thanks, Iain

 

[JLGWhiz]

Do you think there's anything I can do with TRANSPOSE for the bottom part of each data set which is more ordered (F60 onwards in the 1st data set) to convert a 4 column x 8 row table into a single row of data?

Copy/PasteSpecial Transpose:=True would change the data from vertical to horizontal, but setting up the variables and loops to do that is the problem because they would need to be done one column at a time. There you run into the varying span per data set again. What you want done is doable but it would require some effort and complex code that I am not willing to get into. As I said, somebody else might pick up on it and offer a solution. I just do not want to put the time into it to try and put it together.
Regards, JLG