How to copy cells from template.xml (2003 file) to mydoc.xltm

Question

Need help with code to copy cells(range/whole sheet) from second workbook.

Macro is used inside 094-0.....macro.xlsm file and I need to copy first sheet (or range A1:T10 at least) from second file to last sheet from fist file. Then close the second file without saving changes.

I have problem with activation of wokrbook and paste data.

Dim wbk As Workbook

Sheets.Add(after:=Sheets(Sheets.Count)).name = artikl & "_SK" & i 'it's OK 

strSecondFile = "C:\…..\template_2003_XML.xml"
strFirstFile = "C:\…..\094-022605-00001_w_macro.xlsm"

Set wbk = Workbooks.Open(strSecondFile)

    Range("A1:T350").Copy

Set wbk = Workbooks("094-022605-00001_w_macro").Activate

With wbk.Sheets(sheets.Count)
    Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
End With

Workbooks("template_2003_XML.xml").Close savechanges:=False

score 0 · Accepted Answer · answered May 02 '19 at 07:22

You don't need to .Activate just set the workbook without it. • You might benefit from reading How to avoid using Select in Excel VBA.

Also spcify in which workbook and worksheet this range is Range("A1:T350"). Note that you Set wbk twice to two different workbooks. So you should use two differen variables too, like wbkSource and wbkDestination.

Also your with statement With wbk.Sheets(sheets.Count) is useless unless you tell your range a starting . that it should use tha with statement: .Range()

Sheets.Add(After:=Sheets(Sheets.Count)).Name = artikl & "_SK" & i 'it's OK 

Dim SourceFilePath As String
SourceFilePath = "C:\…..\template_2003_XML.xml"

Dim DestinationFilePath As String
DestinationFilePath = "C:\…..\094-022605-00001_w_macro.xlsm"

Dim wbkSource As Workbook
Set wbkSource = Workbooks.Open(SourceFilePath)

Dim wbkDestination As Workbook
Set wbkDestination = ThisWorkbook 'Workbooks("094-022605-00001_w_macro")


wbkSource.Worksheets("source sheet").Range("A1:T350").Copy    
With wbkDestination.Sheets(sheets.Count)
    .Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

wbkSource.Close SaveChanges:=False

I recommend always to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration.

How to copy cells from template.xml (2003 file) to mydoc.xltm

1 Answers1