Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Has anyone got an efficient way to do this?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Has anyone got an efficient way to do this?
In the columns that you mention can the data be added to the bottom of the data in that coulmn and then sorted?, you havent said which columns to which columns? when you say append should the whole row be appended with the data imported i.e if the value appears in the destination sheet then copy the whole row over.....we need a little more to go on! -- Simon Lloyd Regards, Simon Lloyd 'www.thecodecage.com' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=6961 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Has anyone got an efficient way to do this?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Has anyone got an efficient way to do this?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Has anyone got an efficient way to do this?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Has anyone got an efficient way to do this?
"michael.beckinsale" wrote: 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. Again many thanks for your help, Regards Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
More Efficient IF | Excel Programming | |||
More Efficient IF | Excel Programming | |||
What is more efficient | Excel Discussion (Misc queries) | |||
Is there a more efficient way to do this? | Excel Programming | |||
More efficient way? | Excel Programming |