issues copying data from one file to another

[rockhammer]

Hello, I'm trying to copy data from one file to another. The data is copied
almost "as is" except the columns are essentially rearranged. I'm doing it
using the following code (as opposed to copying & pasting whole columns one
at a time) because I want to be able to skip some rows that don't actually
contain data. I have 3 questions based on the code excerpt below ("..." =
code not shown). Please keep in mind the source file was opened using a macro
from the destination file and the source file is presently active:

....
for j = 1 to sourceLastCell.Row ' the last row in the source file
...
entryA = ActiveWorkbook.Worksheets(sourceTab).Cells(j,sourceColA)
' A is a number formatted as "00000000", i.e. leading zeros if not 8
digits long
entryB = ActiveWorkbook.Worksheets(sourceTab).Cells(j,sourceColB)
' B is alphanumeric
...
' the reason I pull the values into entryA & entryB first is that I need to
' determine if I need to skip copying the row of data
' destRange points to a single cell in the destination file
...
destRange.Offset(k,destColA).Value = format(entryA,"00000000")
destRange.Offset(k,destColB).Value = entryB
...
k = k + 1 ' row index in the destination file
next j
....

This code works insofar as the data I want copied are all copied to the
right cells. However my 3 questions are:

1) All the data copied are effectively "copied with formats" but I want
effectively to "copy & paste values". Is there a way to modify this code so
the cell formats are not copied to the destination?

2) the Format() code does not do what I expect it to do. In other words, a
number with fewer than 8 digits are copied without the leading zeros I'm
trying to force onto it. Is there a way to make sure the leading zeros are
there?

3) Compared copying whole columns one at a time, this method because it goes
to the files for each individual cell takes considerably longer time to
execute. I am in debug mode doing just ~6,000 rows at one time with ~13
columns/cells to copy for each row. My code is completing the ~6,000 rows in

1min compared to the straight whole column copy/paste which takes <5sec (all

times rough estimates). I need to scale this eventually to ~15,000 rows. Is
there a smarter/faster way to do this?

Thanks a lot.

Rockhammer

 

[Dave Peterson]

#1. The code you posted doesn't copy formats. It just assigns values. These
two lines do the work:

destRange.Offset(k,destColA).Value = format(entryA,"00000000")
destRange.Offset(k,destColB).Value = entryB

#2. When you type 0000003 into a cell that's formatted as General, excel sees
what you're typing and knows it's just 3. Same thing when you do the "typing"
in code.

You could change the numberformat of that cell:

with destRange.Offset(k,destColA)
.numberformat = "00000000"
.Value = entryA
end with

Or you could force the cell to be text:

with destRange.Offset(k,destColA)
.numberformat = "@"
.Value = format(entryA,"00000000")
end with

or more simply:
destRange.Offset(k,destColA).Value = "'" & format(entryA,"00000000")

#3. Maybe you can exclude the rows you don't want to copy by applying
data|Filter|autofilter and hiding those rows that should be ignored. The
copy|paste the visible cells????

 

[rockhammer]

Hi Dave, thanks for your suggestions.

Your suggestion for #2 works very well. Yes, I neglected that it's the
destination that needs the formatting, not the source.

As to #1, I agree with you that the code I have should not copy formats -
and perhaps I should qualify what I meant with the observation that NOT ALL
formats were copied (e.g. background shading, font colour, etc., were not
copied). However, I had formatted my destination cells to "centre justify"
and after running the code, none of the cells with values assigned retained
the justification format and all became (what looks like) "general" cell
format, i.e. text aligned to the left, numbers to the right, and nothing
centre justified. It's weird, or at least I can't explain it.

As to #3, the reason I need to skip some of the rows is that after copying
the data I am going to do some percentile calculations on some of the
columns. But the data has a lot of straggling cells with things like totals,
averages, excess zeroes, in between the actual data that I need to get rid of
before I can run the calculations. So hiding it doesn't serve the purpose. I
can do the straight copy/paste individual columns and then in a second pass
go row by row to figure out which I need to clear and then sort the result to
get there. That might be faster.

At the end of the day, the cell justification and speed issues I can live
with. The critical problem was the format() one which you helped me solve. So
thanks a lot.

Rockhammer