Has anyone got an efficient way to do this?

[michael.beckinsale]

Hi All,

Has anybody got any code or can point me in the right direction to do
the following efficiently:

Example:

Sheet1 : Main worksheet with 80 columns of data and x rows
Sheet2 : Data to be appended to Sheet1 say 20 columns and x rows
Sheet3 : Data to be appended to Sheet2 say 60 columns and x rows

The difficulty here is that Sheet2 has 20 columns and only the columns
that exist in Sheet1 should be appended to Sheet1. The column titles/
headers/text in each sheet are identical but because the number of
populated columns is only 20 the layout is different. Also where the
same columns do exist they may not be in the same order (eg Sheet1
ColA = Apples, ColB = Pears......Sheet2 ColA = Pears, ColB = Apples)

Sheets3 has the same problems.

Hope l have explained this clearly

All suggestions gratefully appreciated

Michael

 

[JBRC Chorus]

You could try this code in a new module

Option Explicit

Public Sub AppendSheet2()
Call AppendRows(Sheet1, Sheet2)

End Sub
Public Sub AppendSheet3()
Call AppendRows(Sheet1, Sheet3)

End Sub

Public Sub AppendRows(oMaster As Worksheet, oAddSheet As Worksheet)

Dim bBlankrow As Boolean
bBlankrow = False
Dim iRow As Long, iCol As Long

'Find the first blank row in the master sheet
For iRow = 1 To 65000
bBlankrow = True
For iCol = 1 To 80
If Len(oMaster.Cells(iRow, iCol).Value) <> 0 Then
bBlankrow = False
Exit For
End If
Next iCol
If bBlankrow Then Exit For

Next iRow

Dim iInsertPoint As Long
iInsertPoint = iRow


'Map the columns of the sheet to merge to columns of the master sheet

Dim oInsertCol As Range
Dim sHeading As String
Dim iTargetCol(100) As Long
Dim iColsToCopy As Long

Dim oMasterHeading As Range
Set oMasterHeading = oMaster.Range("A1", "DZ1")

For iCol = 1 To 100
sHeading = oAddSheet.Cells(1, iCol).Value
If Len(sHeading) = 0 Then Exit For
iColsToCopy = iCol
Set oInsertCol = oMasterHeading.Find(sHeading, , xlValues,
xlWhole)
If oInsertCol Is Nothing Then
iTargetCol(iCol) = -1
Else
iTargetCol(iCol) = oInsertCol.Column
End If

Next iCol

'Loop through the sheet to merge, transferring values
'from each column into the corresponding master column

Dim sValue As String
For iRow = 2 To 65000
bBlankrow = True
For iCol = 1 To iColsToCopy
sValue = oAddSheet.Cells(iRow, iCol).Value

If iTargetCol(iCol) > -1 Then
oMaster.Cells(iInsertPoint, iTargetCol(iCol)).Value =
oAddSheet.Cells(iRow, iCol).Value
End If
If Len(sValue) > 0 Then bBlankrow = False
Next iCol
If bBlankrow Then Exit For
iInsertPoint = iInsertPoint + 1

Next iRow
End Sub

 

[michael.beckinsale]

Hi Simon,

Sorry for any confusion, l thought my explanation re column headers
would imply which columns to which columns.

Let me clarify, hopefully!

As per the example Sheet2 ColA labelled Pears should be appended to
the Sheet1 ColB labelled Pears. Column data only (excl Label). No
sorting required.

So the process goes something like this, loop thru each column in
Sheet2 and if that column's 'label' (Row1) can be found anywhere in
Sheet1 (Row1) the data should be appended to that column in Sheet1.
Repeat process for Sheet3

I can write the code with loops, finds etc but l am pretty sure that
would be inefficient and that there is a better way. Maybe local named
ranges?

Any thoughts appreciated

Regards

Michael

 

[michael.beckinsale]

Hi JBRC Chorus,

Many thanks for the code which appears to work very well.

I have not had time to exhaustively check the results but at 1st
glance everything looks fine.

I will probably make some small amendments (determine last column,
last row etc) to make the code as efficient as possible and also
because it will be run on Excel 2007.

Is there anything in particular l should look out for?

Again many thanks for your help,

Regards

Michael

 

[JBRC Chorus]

:

Many thanks for the code which appears to work very well.

You're very welcome

I will probably make some small amendments (determine last column,
last row etc) to make the code as efficient as possible....

There is probably a more efficient way of finding the last row in the master
sheet.

Another good optimisation would be:
- First find out how many rows are to be appended using some efficient
algorithm
- Then, in the loop that matches columns between source and destination, use
Copy for all the values in the source col starting at row 2 ending at the
last row and paste values into the master sheet col starting at the first
blank row. That would be very quick and avoids a loop down the rows.

I also suggest you save and turn off calculations/screenupdating at the
start of the function then restore at the end.

and also
because it will be run on Excel 2007.
Is there anything in particular l should look out for?

I don't know of any specific 2007 points.