Help/Advice copying from one sheet to another

[Jeff W.]

I need to copy data from one sheet to another in a macro
the destination sheet is formatted with borders and the like
and the data is coming from another sheet that gets created
but when I copy cell date from one page to another sheet
my cell formats get screwed up

Not sure what the best way to do this is without having to re-format
each cell after the paste.

The cvs file I read in varies in length but is always 6 columns wide
some cells contain string data and some numeric values

The number of rows always varies.

any suggestions?

Regards,

Jeff W.

 

[Guest]

Hi Jeff -

How about something like these 3 statements (replace range addresses and
sheet names to suit):

Worksheets("Sheet1").Range("A1").CurrentRegion.Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

 

[Jeff W.]

The cell I am coming from is a single cell and the one's I'm going to
some are merged and this errors out, with a message about merged cells
and the debugger comes up


<Jeff>

 

[Guest]

Hi Jeff -

In the case of merged cells at the destination, we'll need to decide how you
want the source cells to "go into" the merged destination cells. In other
words, if you have a 6 column by 5 row source (from the .csv) and you're
copying those 30 cells into a 23 cell destination, we'll need more specific
information about which source cells to disregard prior to pasting.

For example, row 2 of a 6 column by 5 row source contains 6 cells. If row 2
of the destination has cells A2-C2 merged, then there are only 4 destination
cells; 6 source cells won't fit. The operation cannot occur unless we
programmatically turn the 6 cells into 4 prior to the paste. We need more
specific rules from you on how to handle this case.

 

[Jeff W.]

Jay, I'm copying 6 columns wide and on my temporary sheet
its all a single cell entry from the cvs file

This is what I need to happen, this would copy data from sheet1
to sheet2 transferring the columns 6 wide

Sheet1 A1 copy to Sheet2 A11 single cell destination
Sheet1 B1 copy to Sheet2 B11 single cell destination
Sheet1 C1 copy to Sheet2 C11 "three merged cell destination
Sheet1 D1 copy to Sheet2 F11 single cell destination
Sheet1 E1 copy to Sheet2 G11 "two merged cell" destination
Sheet1 F1 copy to Sheet2 I11 "four merged cell" destination

and go to the next row and as long as it has data in it, loop and do
the same thing until we are out of data in the 1st cell of the next row

It sounds like what you are telling me is that my temporary sheet, needs
to be formatted the same as my destination sheet, before I can copy the
data across from one to the other is this correct?

I have tried this and when I read in the cvs file, it doesnt follow the cell
layout, it sort of does what it wants


<Jeff>

 

[Guest]

Hi Jeff - The procedure below uses the original pastespecial method if the
destination is not merged and if it is merged, the procedure loops assigning
values cell-by-cell until it runs out of data as you suggested.

Sub JeffW_02()
For Each col In ActiveSheet.UsedRange.Columns
dC = Choose(col.Column, 1, 2, 3, 6, 7, 9) 'dC = destination Column
(number)
col.Copy
If dC <> 3 And dC <> 7 And dC <> 9 Then 'if destination isn't merged,
paste
Worksheets("Sheet2").Columns(dC).PasteSpecial Paste:=xlPasteValues
Else 'if destination contains merged cells, assign values cell-by-cell
Set cel = col.Cells(1, 1)
Do Until cel.Value = ""
Worksheets("Sheet2").Cells(cel.Row, dC).Value = cel.Value
Set cel = cel.Offset(1, 0)
Loop
End If
Next 'col
End Sub

 

[Jeff W.]

I cut and pasted this into my workbook, but it errors out
for some reason, it cant get the single cell data into the merged cells
on sheet1 the data starts at cell A1 and in sheet2 "destination" it
begins at cell A11, I don't know if this is the problem or not. but when
it does stop, I can see the first Colum on sheet1 has been selected
but the whole page of the destination sheet is also selected.

I know this is possible, I have another workbook from a few years back
that is doing this same thing, except I didn't write the code, I just pieced
it all together and fooled with it till I got it to work...

I remember, that we copied the data from sheet1 to sheet2 on the lower
portion of the page below all my other stuff then transferred the data up
above then deleted the lower portion of the page, but exactly what was
done, or why, I'm not clear...

I'll try anything...

<Jeff>

 

[Guest]

Hi Jeff -

The code I sent should paste starting at cell A1 on the destination sheet,
but it sounds like you want it to start in cell A11. Although a small
adjustment to start pasting into cell A11 can be made, it won't avoid the
error you describe. We'll need to clearly redefine the problem before we can
get to the solution.

If you could email me a test workbook, it would help us to focus accurately
on the issue. Also, if you think it will help, send the code you pieced
together, too. Email to jc.sresearch@~h~o~t~m~a~i~l.~c~o~m (remove the "~"
characters).

 

[Guest]

To Readers -

We solved Jeff's problem offline, but I'm posting solutions here for
readers. Although the readers won't have the benefit of Jeff's probelmatic
workbook, the thread should clearly describe the common problem of trying to
paste into merged cells.

Two solutions follow. The first pastes columns if the destinations are
predetermined to be single columns, but programmatically assigns values to
columns that contain merged cells (columns 3, 7, and 9 in Jeff's example).
The second solution uses programmatic assignments for all of the data.

Readers should note that either solution involves programmatically assigning
values into the left-most cell of a group of merged cells. For example, if
columns 3, 4, and 5 of row 2 are merged, you can assign a value to cell(2,3)
just as if it were not merged and the value will be visible in the merged
cells. Programmatically assigning a value to any of the other cells in the
merged group does not result in an error, but the value is not visible and
all cells in the group remain empty.

Solution I:
Sub JeffW_02()
Application.ScreenUpdating = False
For Each col In ActiveSheet.UsedRange.Columns
dC = Choose(col.Column, 1, 2, 3, 6, 7, 9) 'dC = destination Column(number)
col.Copy
If dC <> 3 And dC <> 7 And dC <> 9 Then 'if destination isn't merged,Paste
Worksheets("Tool_List").Cells(11, dC).PasteSpecial
Paste:=xlPasteValues
Else 'if destination contains merged cells, assign values cell-by-cell
Set cel = col.Cells(1, 1)
Do Until cel.Value = ""
Worksheets("Tool_List").Cells(cel.Row + 10, dC).Value = cel.Value
Set cel = cel.Offset(1, 0)
Loop
End If
Next 'col

'Wrap procedure, register cursor at A1 of Tool_List
Application.CutCopyMode = False
Worksheets("Tool_List").Activate
Range("A1").Select
End Sub

Solution II:
Sub copy_su_modified()
'Logic flow similar to Jeff's original version (cell by cell copying), but
'uses programmatic setting of cell values instead of copying.
'In the destination worksheet, the following columns are merged:
'3,4,and 5
'7 and 8
'9, 10, 11, and 12

Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Tool_List")

Application.ScreenUpdating = False

iterations = ws1.Range("A1").CurrentRegion.Rows.Count

'COPY THE TOOL NUMBER
For i = 1 To iterations
ws2.Cells(i + 10, 1) = ws1.Cells(i, 1)
Next i

'COPY THE TOOL diameter
For i = 1 To iterations
ws2.Cells(i + 10, 2) = ws1.Cells(i, 2)
Next i

'COPY THE TOOL description
For i = 1 To iterations
ws2.Cells(i + 10, 3) = ws1.Cells(i, 3)
Next i

'--------------------------------------------------------

'COPY THE flute length
For i = 1 To iterations
ws2.Cells(i + 10, 6) = ws1.Cells(i, 4)
Next i

'COPY THE LFH
For i = 1 To iterations
ws2.Cells(i + 10, 7) = ws1.Cells(i, 5)
Next i

'COPY THE notes
For i = 1 To iterations
ws2.Cells(i + 10, 9) = ws1.Cells(i, 6)
Next i

'Wrap procedure, register cursor at A1
Sheets("Sheet1").Select
Range("A1").Select
Sheets("Tool_List").Select
Range("A1").Select

End Sub