Macro Basic information needed

vaibhav tandon · Jul 17, 2012

Hi Guys,

I am very much new to VBA and was trying hands on various copy paste methods.I wnated to know basic code that world copy and paste the values in a worksheet from another worksheet when BOTH ARE CLOSED.I managed to write a code when both the worksheets are open.I wanted to write when they are closed.Any help would be appreciated.

Code for Opened workbooks

"

Sub test()

workbooks("Test1.xlsx").sheets("sheet1").range("A1:B5").copy _

workbooks("Test2.xlsx").sheets("sheet2").range (A1:B5")

Thanks and Regards

Vaibhav

Luke M · Jul 17, 2012

You'll have to open at least the destination workbook. Using the code from here:

http://www.rondebruin.nl/copy7.htm

your macro could look like this. Note that you'll need to change the file paths to match your need. Current code does not save or close Test2.xlsx, but you could easily add that to the end of the MainMacro.

[pre]

Code:

Sub MainMacro()
Dim SourceBook As String
Dim SourcePath As String
Dim SourceSheet As String
Dim SourceRange As String
Dim DestRange As Range

Application.ScreenUpdating = False
Workbooks.Open FileName:=&#34;C:My DocumentsTest2.xlsx&#34;
Set DestRange = ActiveWorkbook.Worksheets(&#34;sheet2&#34;).Range(&#34;A1:B5&#34;)

SourceBook = &#34;Test1.xlsx&#34;
SourcePath = &#34;C:My Documents&#34;
SourceSheet = &#34;sheet1&#34;
SourceRange = &#34;A1:A5&#34;

On Error Resume Next
Call GetRange(SourcePath, SourceBook, SourceSheet, SourceRange, DestRange)
On Error GoTo 0
Application.ScreenUpdating = True

End Sub

Sub GetRange(FilePath As String, FileName As String, SheetName As String, _
SourceRange As String, DestRange As Range)

Dim Start

&#39;Go to the destination range
Application.Goto DestRange

&#39;Resize the DestRange to the same size as the SourceRange
Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
Range(SourceRange).Columns.Count)

&#39;Add formula links to the closed file
With DestRange
.FormulaArray = &#34;=&#39;&#34; & FilePath & &#34;[&#34; & FileName & &#34;]&#34; & SheetName _
& &#34;&#39;!&#34; & SourceRange

&#39;Wait
Start = Timer
Do While Timer &#60; Start + 2
DoEvents
Loop

&#39;Make values from the formulas
.Copy
.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
End With
End Sub

[/pre]

vaibhav tandon · Jul 18, 2012

Thanks Luke for the reply and post.It works perfectly.

Regards

vaibhav

Luke M · Jul 18, 2012

Glad I was able to help, and thanks for the feedback. =)

Macro Basic information needed

vaibhav tandon

Member

Luke M

Excel Ninja

vaibhav tandon

Member

Luke M

Excel Ninja