Copy & Paste

[Harley78]

I am trying to Copy every other Cell in Row 1 and paste it into another worksheet, transposed starting in A2. Just wondering if someone could help me with what I am missing. What I have so far

Range("A2").Select
Sheets("Configuration Sheet1").Select 'SHEET 1
Range("H1,J1,L1,N1,P1,R1,T1,V1,X1,Z1,AB1,AD1,AF1,AH1,AJ1,AL1,AN1,AP1,AR1,AT1,AV1,AX1,AZ1, BB1, BD1,BF1,BH1,BJ1,BL1,BN1,BP1,BR1,BT1,BV1,BX1,BZ1, CB1, CD1,CF1,CH1,CJ1,CL1,CN1,CP1,CR1,CT1,CV1,CX1,CZ1,DB1, DD1,DF1,DH1,DJ1,DL1,DN1,DP1,DR1,DT1,DV1,DX1,DZ1,EB1, ED1,EF1,EH1,EJ1,EL1,EN1,EP1,ER1,ET1,EV1,EX1,EZ1,FB1, FD1,FF1,FH1,FJ1").Select
Range("FJ1").Activate
Selection.Copy
Sheets("COMBINED CONFIGURATION").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

 

[Jonmo1]

Is there anything in the "between" cells ? I1, K1, M1, O1 etc.. ?

Is there some fundamental difference between the ones you want to copy and the ones you don't (other than the fact they are every other row)...

maybe the ones you want are numbers, while the others are text...
something like that...

 

[Harley78]

Example, H1 has the configuration model No. and I1 has the cost for this configuration.

H1 e.g. 5410-6020(2)M-UT but could and of course I1 is dollar amount.

Hope that helps a little

Excel Workbook
	C	D	E	F	G	H	I
1	Object ID	Object description	Direct Mat $	Item Status	Total Cost	5410-6020(0)M-UT	$ 80,510.40
2		551 SHT FDR (LOW SPEED)
3	5128730000	551 BASE HS SHEET FEEDER	$ 0.01	V	$ 0.01
4
5		310P SOLO CUTTER (PINLESS)
6	5078740000	ASSY - 310S INTERFACE BOX	$ 153.91	S	$ 153.91	1	$ 153.91
7	5079190000	LONG LIFE BLADES - 45 DEG	$ 206.40	V	$ 234.18		$ -
8	5079210000	KIT-CENTER SLITTER	$ 316.62	V	$ 359.24		$ -
9	5079220000	PAPER LOOP CONTROL SWITCH	$ 404.49	V	$ 458.93	1	$ 458.93
10	5079230000	MARGIN TRIMMER MAX. 35MM	$ 162.50	V	$ 184.37		$ -
11	5079240000	WASTE BIN- 310 SPEED&310 STREAM CUTR	$ 151.42	V	$ 171.80		$ -
12	5079250000	ADJUSTABLE LEG	$ 20.19	V	$ 22.91	1	$ 22.91
13	5079270000	ANTISTATIC FOR CHOPPER	$ 0.01	V	$ 0.01	1	$ 0.01
14	5079600000	TRANSPORT ROLLERS	$ 17.54	V	$ 19.90		$ -
15	5079610000	BELT PULLEY, 60HZ SINGLE BLADE	$ 159.33	V	$ 180.78		$ -
16	5079620000	310S BASE CUTTER-PINLESS STREAM	$ 28,425.00	V	$ 32,251.01	1	$ 32,251.01
17	5079630000	JAM DETECTION	$ 0.01	V	$ 0.01	1	$ 0.01
Configuration Sheet1

 

[Jonmo1]

So it's every other cell from H to FJ. H J L N P etc... FJ are all TEXT, where the inbetween I K M O Q etc.. FI are Numbers right?

Give this a shot

Sheets("Configuration Sheet1").Range("H1:FJ1").SpecialCells(xlCellTypeConstants, 2).Copy
Sheets("COMBINED CONFIGURATION").Range("A2").PasteSpecial _
Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

 

[Harley78]

that worked perfectly! thanks Jon


So to do the same for the Pricing eh? Just by changing the H to I and FJ to FK

 

[Jonmo1]

No.

the specialcells command is what told it to get the text...

so change
SpecialCells(xlCellTypeConstants, 2)

to
SpecialCells(xlCellTypeConstants, 1)

that will make it get numbers instead...

 

[Harley78]

ah got, sorry about that,,, thanks again