How to skip an entire column when copy pasting from another sheet

**ckgeary** · 06-20-2013, 11:12 AM

Good morning all!
I am in need of some assistance

I have this macro below that copies cells from "Import_file" to "Master Inst List". The infomation in the "Import_file" sheet is brought in from a .txt file that is generated by AutoCAD and then brought into excel. The macro looks for the record no. (column B) in the "Import_file" and then looks for that same record no. in the "Master Inst List". When the macro finds a match it copies the entire row related to that record no. and pastes it into the matching record no. it found.

Sub Match_Copy_into_list()
Dim i As Integer
Dim m As Variant
Sheets("Import_export").Select

ActiveWorkbook.RefreshAll

    Range("B2").Select
    ActiveWorkbook.Worksheets("Import_file").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Import_file").Sort.SortFields.Add Key:=Range("B2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Import_file").Sort
        .SetRange Range("A2:AZ10000")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    
For i = 1 To 10000
    On Error Resume Next
    m = Application.WorksheetFunction.Match(Cells(i, 2), Sheets("Master Inst List").Range("B:B"), False)
        If Err.Number = 1004 Then GoTo Cycle
            Cells(i, 1).Resize(1, 50).Copy Sheets("Master Inst List").Cells(m, 1)
Cycle:
Next


    
End Sub

Here is my question.......... I would like the macro to copy and paste all of the columns in the row related to the matching record no. except for column H. Is there a way to have to tell the macro to copy all rows but to skip over column H? Am I making any sense???

I hope you can help! I have pasted my worksheet below

**tehneXus** · 06-20-2013, 12:44 PM

Hi,

this is not skipping the column where your formula is but it will insert the formula in the column, performance improved:

Option Explicit

Sub Match_Copy_into_list()
    Dim xlWsTrgt As Worksheet
    Dim xlRng As Range
    Dim i As Integer
    
    Application.ScreenUpdating = False
    
    Set xlWsTrgt = Worksheets("Master Inst List")
    With ActiveWorkbook.Worksheets("Import_file")
    
'        .RefreshAll
    
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SetRange .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
        With .Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
            
        For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row

            Set xlRng = xlWsTrgt.Columns(2).Find(What:=.Cells(i, 2).Formula, LookIn:=xlFormulas, LookAt:=xlWhole)
            If Not xlRng Is Nothing Then
                xlWsTrgt.Range(xlWsTrgt.Cells(xlRng.Row, 3), xlWsTrgt.Cells(xlRng.Row, 26)).Value = Array( _
                    .Cells(i, 3).Value, .Cells(i, 4).Value, .Cells(i, 5).Value, .Cells(i, 6).Value, _
                    .Cells(i, 7).Value, "=IF(RC[-1]=0,RC[2]&""-""&RC[1]&RC[3]&RC[4]&RC[5],RC[-1])", _
                    .Cells(i, 9).Value, .Cells(i, 10).Value, .Cells(i, 11).Value, .Cells(i, 12).Value, _
                    .Cells(i, 13).Value, .Cells(i, 14).Value, .Cells(i, 15).Value, .Cells(i, 16).Value, _
                    .Cells(i, 17).Value, .Cells(i, 18).Value, .Cells(i, 19).Value, .Cells(i, 20).Value, _
                    .Cells(i, 21).Value, .Cells(i, 22).Value, .Cells(i, 23).Value, .Cells(i, 24).Value, _
                    .Cells(i, 25).Value, .Cells(i, 26).Value)
                
            End If
        Next i
    
    End With
Proc_Exit:
    Application.ScreenUpdating = True
    Exit Sub
    
ErrorHandler:
    MsgBox Err.Number & ": " & Err.Description, vbCritical + vbOKOnly, "Error"
    Resume Proc_Exit
End Sub

**ckgeary** · 06-20-2013, 05:52 PM

Thanks that work so great!! Could you possibky help with one more thing? At times I need the information to go the opposite direction from the "Master Inst List" to the "Import_file". I have a similar code to the original one I posted but sometimes it leaves numbers out....... I tried to modify your code you did but it wasnt working right.

**tehneXus** · 06-21-2013, 12:33 PM

Hi,

few questions on that:
1. Do you want to copy ALL records from "Master Inst List" to "Import_file" or is there the "REC_NO" in "Import_file" already to match? or do you want to enter a range of "REC_NO"?
2. Are there headings prepared in "Import_file"?
3. Column A "HANDLE" is not in "Master Inst List", what to do?

**ckgeary** · 06-21-2013, 12:51 PM

Thanks again for your help

So here is the breakdown.......
I have a .txt file that I import into Excel that was original generated by information in AutoCAD and I read and write to this txt file from the Excel workbook. The record no. is what I am using to "link" files from one sheet to another. So both the "Import_file" and the "Mast Inst List" both have this record number listed in the "B" column of their sheets. I had 2 macros that I was using, one for moving info from the "Import_file" to the "Master Inst List" and another for moving the information from "Master Inst List" to the "Import_File". I was having an issue with needing to replace that formula in column "H" of the "Master Inst List" after I ran the macro to move information into the "Master Inst List". The macro that you shared with me (thanks again!) I am now using to move info from the "Import_File" to the "Master Inst List" and it is great! Now I am having issues with the macro I have for moving information from the "Master Inst List" to the "Import File". It seems to work for the most part but for somereason it will randomly leave certain record numbers out of the transfer and I cannot figure out why. That is why I was hoping to use that code you shared just in reverse (from "Master Inst List" to "Import_file"). I have been told to use VLOOKUP() but I cannot have code in the "Import_file" because I have to import that information back into AutoCAD.

This is the code I am using to move files from "Master Inst List" to "Import File", but I would like to use something similar to what you shared with me...... It seems to work better!

Sub Match_Copy_out_of_list()
Dim i As Integer
Dim m As Variant
Sheets("Master Inst List").Select
Range("B14").Select
    ActiveWorkbook.Worksheets("Master Inst List").SORT.SortFields.Clear
    ActiveWorkbook.Worksheets("Master Inst List").SORT.SortFields.Add Key:=Range("B2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Master Inst List").SORT
        .SetRange Range("A14:AZ10000")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
For i = 1 To 5000
    On Error Resume Next
    m = Application.WorksheetFunction.Match(Cells(i, 2), Sheets("Import_file").Range("B:B"), False)
        If Err.Number = 5004 Then GoTo Cycle
            Cells(i, 1).Resize(1, 50).Copy Sheets("Import_file").Cells(m, 1)
            
Cycle:
Next

Sheets("Import_file").Select
ActiveWorkbook.SaveAs Filename:= _
        "\\STORAGE\Project\2012\12334-5010-01-0358 - FAM - Suncor Fort Hills Crushing, Surge Facility & Conveyors\30 Design\Lists\Instrument List\list support Files\Import_file.txt", _
        FileFormat:=xlText, CreateBackup:=False
    ActiveWorkbook.SaveAs Filename:= _
        "\\STORAGE\Project\2012\12334-5010-01-0358 - FAM - Suncor Fort Hills Crushing, Surge Facility & Conveyors\30 Design\Lists\Instrument List\SC610-P-8001_REVB.xlsm", FileFormat _
        :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        
End Sub

**ckgeary** · 06-21-2013, 01:01 PM

Oh sorry the handle is a number that autocad need to read. I used your code and modfied it to bring that into the "Master Inst List" so that when I move info from the "Master Inst List" to the "Import File" that HANDLE does not get lost. It is what AutoCAD uses to recognize the information.....

**tehneXus** · 06-21-2013, 01:14 PM

Hi,

this is basically the same code but copies the other way around. Please pay attention to the sorting as your Record ID's are changing whenever you sort and therefore the match will change, maybe remove the sort from code. Workbook is saved at the end and kept open.

Sub Match_Copy_out_of_list()
    Dim xlWsTrgt As Worksheet
    Dim xlRng As Range
    Dim i As Integer
    
    Application.ScreenUpdating = False
    
    Set xlWsTrgt = Worksheets("Import_file")
    With ActiveWorkbook.Worksheets("Master Inst List")
        
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Cells(14, 2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SetRange .Range(.Cells(13, 2), .Cells(.Cells(.Rows.Count, 2).End(xlUp).Row, .Cells(11, .Columns.Count).End(xlToLeft).Column))
        With .Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
            
        For i = 14 To .Cells(.Rows.Count, 2).End(xlUp).Row

            Set xlRng = xlWsTrgt.Columns(2).Find(What:=.Cells(i, 2).Value, LookIn:=xlFormulas, LookAt:=xlWhole)
            If Not xlRng Is Nothing Then
                xlWsTrgt.Range(xlWsTrgt.Cells(xlRng.Row, 3), xlWsTrgt.Cells(xlRng.Row, 26)).Value = Array( _
                    .Cells(i, 3).Value, .Cells(i, 4).Value, .Cells(i, 5).Value, .Cells(i, 6).Value, _
                    .Cells(i, 7).Value, "=IF(RC[-1]=0,RC[2]&""-""&RC[1]&RC[3]&RC[4]&RC[5],RC[-1])", _
                    .Cells(i, 9).Value, .Cells(i, 10).Value, .Cells(i, 11).Value, .Cells(i, 12).Value, _
                    .Cells(i, 13).Value, .Cells(i, 14).Value, .Cells(i, 15).Value, .Cells(i, 16).Value, _
                    .Cells(i, 17).Value, .Cells(i, 18).Value, .Cells(i, 19).Value, .Cells(i, 20).Value, _
                    .Cells(i, 21).Value, .Cells(i, 22).Value, .Cells(i, 23).Value, .Cells(i, 24).Value, _
                    .Cells(i, 25).Value, .Cells(i, 26).Value)
                
            End If
        Next i
    
    End With
    
    xlWsTrgt.Activate
    With xlWsTrgt.Parent
        .SaveAs _
            Filename:="\\STORAGE\Project\2012\12334-5010-01-0358 - FAM - Suncor Fort Hills Crushing, Surge Facility & Conveyors\30 Design\Lists\Instrument List\list support Files\Import_file.txt", _
            FileFormat:=xlText, _
            CreateBackup:=False
            
        .SaveAs _
            Filename:="\\STORAGE\Project\2012\12334-5010-01-0358 - FAM - Suncor Fort Hills Crushing, Surge Facility & Conveyors\30 Design\Lists\Instrument List\SC610-P-8001_REVB.xlsm", _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
            CreateBackup:=False
    End With
Proc_Exit:
    Application.ScreenUpdating = True
    Exit Sub
    
ErrorHandler:
    MsgBox Err.Number & ": " & Err.Description, vbCritical + vbOKOnly, "Error"
    Resume Proc_Exit
End Sub

**ckgeary** · 06-21-2013, 01:25 PM

BEAUTIFUL!!! I took the sort function out. I thought I needed it to ruin through the numbers but I see that it was just hurting me

I cannot thank you enough!!!

How to skip an entire column when copy pasting from another sheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to skip an entire column when copy pasting from another sheet

Re: How to skip an entire column when copy pasting from another sheet

Re: How to skip an entire column when copy pasting from another sheet

Re: How to skip an entire column when copy pasting from another sheet

Re: How to skip an entire column when copy pasting from another sheet

Re: How to skip an entire column when copy pasting from another sheet

Re: How to skip an entire column when copy pasting from another sheet

Re: How to skip an entire column when copy pasting from another sheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions