Copying data from one Excel Workbook to another through a macro

[philthepowerhouse]

Hi there,

I hope someone can help me as I am at my wits end!

I am trying to automate a boring process and below is the code I am using to do this:

*************************************************

Workbooks.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "1995"
Range("A1").Select
ActiveWorkbook.SaveAs Filename:= _
"F:\PWay\FAME\Corporate Health Check\Final\Time Series\Excel\LQ_M_UQ_Grouped Ratios.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Workbooks.Open Filename:= _
"F:\PWay\FAME\Corporate Health Check\Final\Time Series\Excel\Final 1995.xls"
Sheets("Grouped Ratios").Select
Sheets("Grouped Ratios").Copy Before:=Workbooks("LQ_M_UQ_Grouped Ratios.xls") _
.Sheets(1)
Sheets("Grouped Ratios").Select
Sheets("Grouped Ratios").Name = "1995"
Range("A1").Select
ActiveWorkbook.Save
Windows("Final 1995.xls").Activate
ActiveWorkbook.Close

*******************************************************

Using a macro I want to add a new workbook and open another workbook followed by copying a worksheet from this workbook to the one I have just created and then close it once I have finished.

The macro will run so far e.g. create the new workbook and open the other but I cannot for the life of me get it to copy the data across. I have tried every method of copying and pasting possible but no hope.

Hope someone can help,

Many thanks in advance!

Powerhouse!

 

[longhair]

philthepowerhouse,
i believe you need to copy the data, then activate the new workbook and paste.
something along these lines should help

selection.copy  'copy your data
Workbooks("F:\PWay\FAME\Corporate Health Check\Final\Time Series\Excel\Final 1995.xls").Activate  'seleect the other open workbook
ActiveSheet.Paste  'paste your data
ActiveWorkbook.SaveAs... 'save

hth
regards,
longhair

 

[AnotherHiggins]

FYI: in the future, VBA-specific questions should be posted to forum707 (VBA Visual Basic for Applications (Microsoft) Forum)

This should do what you want:

Sub a_test1()
OrigWB = ActiveWorkbook.name
Workbooks.Open Filename:= _
    "C:\YourPath\FileName.xls"
'If the second workbook has more than one sheet, add the following two commented-out lines:
'TempWB = ActiveWorkbook.name
Sheets(1).Move Before:=Workbooks(OrigWB).Sheets(1)
'Workbooks(TempWB).Close
End Sub

This is the equivalent of doing the following (and you can get similar code by recording yourself doing these steps:
[ul][li]Right click the sheet you want copied[/li]
[li]Choose Move or Copy[/li]
[li]In the 'To Book' section, change it to the WB you want[/li]
[li]Do not choose 'Create a copy'[/li]
[li]Click OK[/li][/ul]

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.