+ Reply to Thread
Results 1 to 11 of 11

Ctrl find, copy and pasting help please (Macro code needed)

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    09-06-2011
    Location
    northern ireland
    MS-Off Ver
    Excel 365
    Posts
    180

    Ctrl find, copy and pasting help please (Macro code needed)

    Hi

    I attach two spreadsheets, one is "Excel Spreadsheet Header Row_1.xlsx" which is a fixed header row that has to stay in the order on the sheet. The other spreadsheet is a variable spreadsheet which changes month to month "example.xlsx". I need the data from the example.xlsx to be copy and pasted into the excel spreadsheet but not all header row items are used.

    My problem is when I search for "Salary Adj", because its not in the example.xlsx the macro stops working and I need a bit of code to rectify it. So If the column "Salary Adj" has no data in the example sheet then I want zero's in the cells within the column with the header "Salary Adj" in the Excel Spreadsheet Header Row.xlsx"

    Here is a selection of the code below:

    
    Sub parttwo()
        
        
        Workbooks.Open Filename:= _
            "N:\PAYROLL\Leanne\Private - Macro Work LB only\Excel Spreadsheet Header Row_1.xlsx"
        
        'Need to sort this out because it can't find sal adj its displaying an error
        
       
        Range("S2").Select
        Cells.Find(What:="Salary Adj", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        Range("T2").Select
        Application.CutCopyMode = False
        With Selection.Font
            .Name = "Microsoft Sans Serif"
            .FontStyle = "Regular"
            .Size = 8
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone
        End With
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 3
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 3
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        With Selection
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlTop
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.NumberFormat = "0.00"
        ActiveCell.FormulaR1C1 = "0"
        Range("T2").Select
        Selection.Copy
        Range("T2:T30").Select
        ActiveSheet.Paste
    Not sure that I put the complete piece of the code in, so for car allowance the same thing happens as below

    
    
     Windows("example.xlsx").Activate
        Range("V30").Select
        
        'same for this as car allow and sal adj
        Cells.Find(What:="Holiday Pay", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        Windows("Excel Spreadsheet Header Row_1.xlsx").Activate
        Cells.Find(What:="Holiday Pay", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        Range("W2").Select
        Application.CutCopyMode = False
        With Selection.Font
            .Name = "Microsoft Sans Serif"
            .FontStyle = "Regular"
            .Size = 8
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone
        End With
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 3
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 3
            .TintAndShade = 0
            .Weight = xlThin
        End With
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        With Selection
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlTop
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.NumberFormat = "0.00"
        ActiveCell.FormulaR1C1 = "0"
        Range("W2").Select
    Attached Files Attached Files
    Last edited by leanne2011; 11-24-2011 at 08:29 AM.

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Ctrl find, copy and pasting help please (Macro code needed)

    I have a question for you - is there any specific reason why you are searching the entries from excel spreadsheet.... in example file and not vice versa? You can search for all the columns from example in the big file and then copy the data. Whichever columns are not found, you can choose to put a 0 or leave blank. What do you say?
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Forum Contributor
    Join Date
    09-06-2011
    Location
    northern ireland
    MS-Off Ver
    Excel 365
    Posts
    180

    Re: Ctrl find, copy and pasting help please (Macro code needed)

    Quote Originally Posted by arlu1201 View Post
    I have a question for you - is there any specific reason why you are searching the entries from excel spreadsheet.... in example file and not vice versa? You can search for all the columns from example in the big file and then copy the data. Whichever columns are not found, you can choose to put a 0 or leave blank. What do you say?
    Hi Arlu

    No - no particular reason other than I am a novice and wanted to ensure that all header items had been searched for in the example file so as all data is captured. The example file is variable and changes month to month so I want the macro to place all the data contained within the excel spreadsheet. The remaining data not copied across will be used in another spreadsheet for which i will build another macro for.

    The end result is for all the example data to go into the excel spreadsheet. I just need to make sure each line doesnt get mixed up as it relates to one employee. The next line data is for a nother employee

    Hope this makes sense - thanks for your help on this

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Ctrl find, copy and pasting help please (Macro code needed)

    You have mentioned that the example file changes each time. Do the headers change too? Or just that today there might be data in 5 columns and the next time 8?

  5. #5
    Forum Contributor
    Join Date
    09-06-2011
    Location
    northern ireland
    MS-Off Ver
    Excel 365
    Posts
    180

    Re: Ctrl find, copy and pasting help please (Macro code needed)

    Quote Originally Posted by arlu1201 View Post
    You have mentioned that the example file changes each time. Do the headers change too? Or just that today there might be data in 5 columns and the next time 8?
    The excel spreadsheet has all the possible payroll components where as the example file will only have the payroll components processed in say this period. The headers will be identically named and will not change from month to month but this month 5 of the components could be used which is what will populate in the example file or next month all the header rows from the excel spreadsheet could be used in which case all payroll components/header names will appear in the example file

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Ctrl find, copy and pasting help please (Macro code needed)

    Ok, since its all inclusive, it will be better to search the headers from the example file in the excel file and not vice-versa. This way, you will not get errors. And incase there are some fields which are not populated, we can have the macro key in blanks or 0.

    Once the header is found, the entire column can be copied right away. You do not need to go row wise, so this can avoid possible errors.

  7. #7
    Forum Contributor
    Join Date
    09-06-2011
    Location
    northern ireland
    MS-Off Ver
    Excel 365
    Posts
    180

    Re: Ctrl find, copy and pasting help please (Macro code needed)

    Quote Originally Posted by arlu1201 View Post
    Ok, since its all inclusive, it will be better to search the headers from the example file in the excel file and not vice-versa. This way, you will not get errors. And incase there are some fields which are not populated, we can have the macro key in blanks or 0.

    Once the header is found, the entire column can be copied right away. You do not need to go row wise, so this can avoid possible errors.
    Thanks Arlu - but the variable sheet will have different headers in it from month to month - any of those headers in the variable sheet can be any of those on the fixed sheet so that is why I couldn't do it the other way. How can I check that any header (which will change from month to month) is in the fixed excel fixed spreadsheet ? what code could I use ?

    Many thanks

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Ctrl find, copy and pasting help please (Macro code needed)

    I am working out something for you. I have a question. When the data is found, should it be copied to the next available row in the excel spreadsheet or just below the header?

  9. #9
    Forum Contributor
    Join Date
    09-06-2011
    Location
    northern ireland
    MS-Off Ver
    Excel 365
    Posts
    180

    Re: Ctrl find, copy and pasting help please (Macro code needed)

    Quote Originally Posted by arlu1201 View Post
    I am working out something for you. I have a question. When the data is found, should it be copied to the next available row in the excel spreadsheet or just below the header?
    The header row in both spreadsheet will be contant so the data when the header/payroll component is found then I want the data pasted directly below the header row so from row 2 onwards

    many thanks

  10. #10
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Ctrl find, copy and pasting help please (Macro code needed)

    Am working on something. Getting an error. Will provide you the code soon.

  11. #11
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Ctrl find, copy and pasting help please (Macro code needed)

    Use this code -
    Option Explicit
    Dim WeeklyFN As String
    Dim MainFN As String
    Dim MFile As String
    Dim lrow As Long
    Dim sfield As String
    Dim cellcol As Long
    Dim i As Long
    Dim lastrow As Long
    Dim c As Object
    
    Sub find_cols()
    
    MFile = ActiveWorkbook.Name
    Application.ScreenUpdating = False
    proceed:
    WeeklyFN = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", Title:="Please open the Weekly file")
    If WeeklyFN = "" Then
        MsgBox "You have not selected a file."
        GoTo proceed
    Else
        Workbooks.Open Filename:=WeeklyFN
        WeeklyFN = ActiveWorkbook.Name
    End If
    
    proceed1:
    MainFN = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", Title:="Please open the Main file")
    If MainFN = "" Then
        MsgBox "You have not selected a file."
        GoTo proceed1
    Else
        Workbooks.Open Filename:=MainFN
        MainFN = ActiveWorkbook.Name
    End If
    
    Workbooks(MFile).Worksheets("Sheet1").Columns("A:A").Delete
    Workbooks(WeeklyFN).Worksheets("Sheet1").Rows("1:1").Copy
    Workbooks(MFile).Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
    Application.CutCopyMode = False
    
    lrow = Workbooks(MFile).Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 1 To lrow
        sfield = Workbooks(MFile).Worksheets("Sheet1").Range("A" & i).Value
        
        Workbooks(MainFN).Worksheets("Sheet1").Range("A2").Select
        On Error Resume Next
        Workbooks(MainFN).Worksheets("Sheet1").Rows("1:1").Find(What:=sfield, After:=Range("A1"), LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, searchformat:=False).Activate
        On Error GoTo 0
        cellcol = ActiveCell.Column
        
        lastrow = Workbooks(WeeklyFN).Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
        With Workbooks(WeeklyFN).Worksheets("Sheet1")
            .Range(.Cells(2, i), .Cells(lastrow, i)).Copy Workbooks(MainFN).Worksheets("Sheet1").Cells(2, cellcol)
        End With
           
        Application.CutCopyMode = False
    Next i
    
    End Sub
    Just for your information - i didnt find the "Branch" field in the Excel Spreadsheet. There could be a few more like that but the code ignores them. Try this code and let me know if the fields have been copied properly. You are the best judge of that.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1