Solved: Open a workbook & copy data using

[lostin_space]

Hi all... i've got what i think is a common problem, but i'm a bit stumped with it...

I need to copy data from workbook 2, into workbook 1 - then store the data on a hidden worksheet, so i can then format it & present it all...

the name of workbook 2 will vary - this is'nt a problem, as i want the user to be prompted with a dialogue box & and for them to point to the right file manually, as it may be stored in different folders etc... -

i think the workbook.open function would do it...

i could'nt find quite what i was after on the kb or in other threads - anyone got some code to hand that can do this?

Cheers

Russ

[malik641]

Are you combining all the sheets into one specified worksheet? Or are you copying each sheet individually and making new sheets in the main workbook you are copying to?

If you're looking for the syntax to copy a worksheet from one workbook into another, you could look at this:
[vba]Workbooks("Workbook_To_Copy_From").Worksheets("Sheet1").Copy _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)[/vba]

[lostin_space]

wanted to prompt the user to point to the file...

used this...

[vba]

Public Sub fileopener()

Dim myFileName As Variant
Dim SourceWkbk As Workbook
Dim CurrentWkbk As Workbook
Dim testWks As Worksheet
Dim DestCell As Range

myFileName = Application.GetOpenFilename("Excel files,*.xls")

If myFileName = False Then
Exit Sub 'user hit cancel
End If

Set CurrentWkbk = ActiveWorkbook
Set SourceWkbk = Workbooks.Open(Filename:=myFileName)

Set testWks = Nothing
On Error Resume Next
Set testWks = SourceWkbk.Worksheets("sheet1")
On Error GoTo 0

If testWks Is Nothing Then
MsgBox "Missing the worksheet!"
Else
With CurrentWkbk.Worksheets("Test")
Set DestCell = .Cells(.Rows.Count, "a").End(xlUp).Offset(1, 0)
End With
With testWks
'choose one of these
.Range("a1", .Cells.SpecialCells(xlCellTypeLastCell)).Copy
DestCell.PasteSpecial Paste:=xlPasteAll

Application.CutCopyMode = False

End With
End If

SourceWkbk.Close savechanges:=False

End Sub
[/vba]

does the trick... think it must just have been a 'brain phart' :-) as i've not done any coding 'in anger' for a while...

[fwawergurl16]

Hi there lostin_space,

I find ur code very applicable for the VBA macro that I'm suppose to solve! I'm an intern with a media company, but im in the IT side. I'm suppose to do a macro that helps copy & paste to combine 5 workbooks. In each workbook is only 1 sheet of table. I want to combine all 5 into just 1 workbook/sheet, so that it'll make pivoting easier. So far, I'm unsuccessful 'til I came across ur codes.

Would u be able to help me in showing me how i could loop the code to make it run 5 times for all the workbooks involved? I've attached some samples of the doc to be combined.

I hope you'll be able to help me since I'm terribly new to VBA (practically know nuts about it!). Thanks loads & sorry for the trouble caused.