Hi there,
I've seen this sort of answered on an old closed thread but i'm hoping someone can help me some with code.
I wish to have a 'master.xlsx' which whe I run a macro, allows me to search and open another workbook 'Entry.xlsx'. The Entry workbook has a number of tabs 'data1', 'data2' that I wish to copy and paste from eache sheet and add to the 'master.xlsx'. The master has the same tabs 'data1' etc. The macro should be able to add data (it's basically compliling 'entry' sheets after every month so the master has the full year data).
hope someone can help. The following code, opens the workbook, but does not copy the data??
hope someone can help
Sub wbcopy()
Dim fn
Dim wbFrom As Workbook
Dim ws As Worksheet
Dim rCopy As Range
Dim sSht As String
On Error Resume Next
fn = Application.GetOpenFilename 'can add parameters. See help for details.
If fn = False Then
MsgBox "Nothing Chosen", vbCritical, "Select workbook"
'now that you have the name, you can open it
Else: Workbooks.Open fn
'set variable to source workbook
Set wbFrom = ActiveWorkbook
For Each ws In wbFrom.Worksheets
With ws
sSht = .Name
'determine range to copy
Set rCopy = .Range(.Cells(4, 1), .Cells(.Rows.Count, 3).End(xlUp))
End With
'copy to relevant sheet in master wb
If Not rCopy Is Nothing Then rCopy.Copy _
ThisWorkbook.Worksheets(sSht).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Next ws
End If
On Error GoTo 0
Set rCopy = Nothing
Set wbFrom = Nothing
End Sub
Bookmarks