2 Excel Workbooks Match, and Copy

[gogreen32]

Currently, I have 2 excel workbooks.

Workbook 1: contains 1 tab (worksheet) generated by a macro to formatt data and name the tab as the month (ie 1,2,3,).

Workbook 2: contains summary tabs (worksheets) that draw on information from monthly data which are in tabs named by month (ie 1,2,3).

Currently, I am copying and pasting from Workbook 1 into workbook 2 to correctly match the months.

I would like a macro to be able to match the name of the tab in workbook 1 to one of the 12 months listed in workbook 2 and paste the information from workbook 1 to 2, into the matching tab.

I would be greatful if someone could figure this out for me. I am still learning VBA. Thanks!

[Hack]

Moved.

[gogreen32]

May I ask what that means?

[si_the_geek]

You posted this thread somewhere that wasn't really appropriate, Hack moved it here so that you should get more help.

[gogreen32]

Oh, ok, thank you. Still would enjoy some help on this problem.

[dglienna]

Welcome to the forums.

You should record a macro to do what you want, and then edit it. Copy and paste it into your VB program, and it will be easy to modify to suit your needs.

[gogreen32]

Thanks for the suggestion, I already tried to do that. The problem is I want the tab in the first workbook to match one of 12 tabs in the second, and when that match is made, have the data transfer over into the correct tab. The recorded macro only selects a particular tab, and does not provide any sort of if tab 1 = tab 2 then, but if not then look at tab 3, and if not that tab, look at 4 and so on to copy the data.

[si_the_geek]

Right then, first of all sorry for the delay in posting a "proper" reply (I've been away from the forums for a few hours).

I think something like this should work for you (I wasn't sure how you want the data copied, so I just put in a couple of examples):

Dim oTargetWorkBook As Workbook
 
Dim iCount As Integer
Dim iTargetSheet As Integer
Dim sSourceSheetName As String
 
  sSourceSheetName = UCase(Trim(ThisWorkbook.Sheets(1).Name))
 
       'Set an object to the target workbook
       '(change [i]Book2[/i] here to the name of your target workbook)
  Set oTargetWorkBook = Application.Workbooks("Book2")
 
  With oTargetWorkBook.Sheets
 
         'Loop through the sheets to to find the right one
    iTargetSheet = -1
    For iCount = 1 To .Count
      If UCase(Trim(.Item(iCount).Name)) = sSourceSheetName Then
        iTargetSheet = iCount
        Exit For
      End If
    Next iCount
    
    If iTargetSheet = -1 Then
         'Couldn't find it, show appropriate message
      MsgBox "Couldnt find the target sheet!", vbCritical
    Else
      With .Item(iTargetSheet)
         'We did find it, so do your pasting!
        'example 1: set a single cell
        .Range("D8") = "hello"
    
        'example 2: copy the all of source sheet to the bottom of target sheet
        ThisWorkbook.Sheets(1).UsedRange.Copy _
           .Range ("A" & (UsedRange.Rows.Count + 1))
 
      End With
    End If
 
  End With
  
      'close reference to target workbook
  Set oTargetWorkBook = Nothing

If you don't understand any of it (or need help to copy as you want), feel free to ask