Results 1 to 5 of 5

Thread: VBA to copy data from multiple workbooks into one sheet if condition met
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to copy data from multiple workbooks into one sheet if condition met

    I may have seriously gotten in over my head with a project when it's been a while since I coded.

    Basically I have a workbook that has a sheet for each month (12) January 2019 to December 2019, I need certain information (Column A B C D E F G H I) to compile to one main sheet in the next available row, without deleting or overwriting any data if Column J = Yes in each of the 12 sheets.

    I feel like I just jumped off deep end with this project! It may not be as difficult as I've let my frustrated brain think it is, if anyone can offer some advice please.

  2. #2
    Board Regular
    Join Date
    Dec 2009
    Posts
    284
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy data from multiple workbooks into one sheet if condition met

    I'm not sure how comfortable you are with coding, but my advice would be to code it this way (pseudo code below)

    Code:
    dim pasteRow as long 'will keep track of next unused row on main sheet for pasting to
    
    pasteRow = lastUserRowOnMainSheet 'there are several ways to find this.
    
    For each sheet in workbook
       If sheet.name <> yourMainSheetsName 'dont do things to mainSheet
          turn off all sheet filters 
          apply an advanced filter to the sheet 'advanced filters could filter column a:j for yes and paste the result to the mainSheet (on the pasteRow) at the same time
          pastRow = lastUserRowOnMainSheet 
       end if
    next sheet
    My favorite Excel Add-in:= Nutilities

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy data from multiple workbooks into one sheet if condition met

    This is what I have and running into error. Please remember I am extremely rusty in coding ability!

    Code:
    For Each ws In ActiveWorkbook.Worksheets
     
          If ws.Name = "Sheet3" Or ws.Name = "Jan" Then
          
            Set columnJ = ws.Range("J:J") 'columnJ
            For Each c In columnJ
              
              If WorksheetFunction.IsText(c.Value) Then
                If InStr(c.Value, "Yes") > 0 Then
                  c.ws.Range("A:I").Copy
                  destinationWorksheet.Cells(count, 1).pasteRow = lastUserRowOndestinationWorksheet
                  count = count + 1
                End If
              End If
            Next c
                
          End If
     
      Next ws
    This line I am getting error.

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy data from multiple workbooks into one sheet if condition met

    My error line didn't post, destinationWorksheet.Cells(count,1).pasteRow is line am getting error on.

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy data from multiple workbooks into one sheet if condition met

    This is my basic code for one sheet, then last part copied for each additional sheet.

    Code:
    Public Sub sCopyRows()
    
      Dim ws As Worksheet
      Dim destinationWorksheet As Worksheet
      Dim columnJ As Range
      Dim c As Range
      Dim pasteRow As Long
     
      Set destinationWorksheet = ActiveWorkbook.Worksheets("BoB")
     
      destinationWorksheet.Cells.ClearContents
     
      count = 1
      For Each ws In ActiveWorkbook.Worksheets("BoB")
     
          If ws.Name = "Sheet3" Or ws.Name = "Jan" Then
          
            Set columnJ = ws.Range("J:J") 'columnJ
            For Each c In columnJ
              
              If WorksheetFunction.IsText(c.Value) Then
                If InStr(c.Value, "Yes") > 0 Then
                  c.ws.Range("A:I").Copy
                  destinationWorksheet.Cells(count, 1).pasteRow = lastUserRowOndestinationWorksheet
                  count = count + 1
                End If
              End If
            Next c
                
          End If
     
      Next ws

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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