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 Book2 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 :)