Columns getting copied incorrectly

[prajakta]

Hi,

I am facing an issue trying to copy columns from one existing excel sheet (say Sheet1.xlsm) to a new dynamically created one (say Sheet2.xls).
Scenario:
Sheet1 has colums A thru AK of which I need to selectively copy some columns into Sheet2

Code for this functionality:
wks- reference to Sheet2/new sheet
ws- reference to Sheet1/old sheet
---------------------------
irow = 2
LastRow = 1
For irow = 2 To totalRows Step 1
wks.Cells(LastRow, "X").Value = ws.Cells(irow, col_custNum).Value
LastRow = LastRow + 1
Next irow
---------------------------
Here, though I specify the destination column as 'X' it always pastes code to column 'M'. There is an offset of about 10 columns for every column I try to paste into the new sheet. I tried creating an intermediate sheet and copying data from that however this issue still persists. The wierd part is untill a few days back it was offesetting the by about 7 columns, so I tweaked the code accordingly. However, now it is offsetting it by 10 columns and I cannot keep changing the code. Any pointers as to why this is happening and some solution will be very helpful.
Thanks,
Prajakta

 

[energman58]

Although you are doing it in a really unusual way (why not simply copy and paste the values in the cells you need which would avoid having to loop through each cell in turn) it should work but I think the problem is in the CELLS property - assuming your references to the worksheets work properly.

CELLS usually takes the form CELLS(Number,Number) (I didnt even know you could use a letter reference - the help and all the books I have all refer to numbers only) so to get to X I would use CELLS(LastRow,24) (X being the 24th column in a WS)

I have no clue as to why it should paste in "M" clearly it is converting "X" into 13 somehow. I have just tried your format and on my version it reads "X" as X (24) so it is a mystery what is happening but if you go to numbers it should be bullet proof.

 

[prajakta]

CORRECTED POST:
Hi,

I am facing an issue trying to copy columns from one existing excel sheet (say Sheet1.xlsm) to a new dynamically created one (say Sheet2.xls).
Scenario:
Sheet1 has colums A thru AK of which I need to selectively copy some columns into Sheet2

Code for this functionality:
wks- reference to Sheet2/new sheet
ws- reference to Sheet1/old sheet
---------------------------
irow = 2
LastRow = 1
For irow = 2 To totalRows Step 1
wks.Cells(LastRow, "L").Value = ws.Cells(irow, col_custNum).Value
LastRow = LastRow + 1
Next irow
---------------------------
Here, though I specify the destination column as 'L' it always pastes code to column 'A'. Thus the first column in the new sheet always starts from 'A' no matter which column I specify. I tried giving column index as well but that didn't help. I always want the first column to be pasted in column L. The column I am copying is in column 'D' in the original sheet (Sheet1). I tried creating an intermediate sheet and copying data from that however this issue still persists. Any pointers as to why this is happening and some solution will be very helpful.
Thanks,
Prajakta

 

[prajakta]

Thanks Energman, however I tried playing around with column number- that didn't help much though.

I am guessing it might have something to do with tab delimited thing or the formats. Note-I am creating a xls file from an xlsm. Just not able to nail this!

 

[energman58]

I woudl just select and copy and paste (special) in that case - as you know how many rows and so on you need and the location. You can use a name for your destination cell. It will also be a lot faster as you will avoid all that looping