You are here:

Excel/Macro- transfer data

Advertisement


Question
Hi Tom,

I received a Compile Error: Duplicate declaration in current scope

bk As Workbook was highlighted...

Did I not change something? I changed the following:
s = "C:UsersmccongkDesktopTest2.xlsm"  '<= change
s1 = "Test2.xlsm"  '<== change
Set sh2 = bk.Worksheets("Sheet1") '<= changed line


I left the given range for testing purposes...


This is what I have:


Private Sub CommandButton1_Click()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, rw As Long, bk As Workbook
Dim s As String, bk As Workbook

Set sh1 = Worksheets("Sheet1")

s = "C:UsersmccongkDesktopTest2.xlsm"  '<= change
s1 = "Test2.xlsm"  '<== change
On Error Resume Next
Workbooks.Opens s
On Error GoTo 0
Set bk = ActiveWorkbook

If InStr(1, bk.Name, s1, vbTextCompare) = 0 Then
MsgBox s1 & " was not opened - quitting"
Exit Sub
End If

Set sh2 = bk.Worksheets("Sheet1") '<= changed line

Set r1 = sh1.Range("A2:H2")  '<== change to the range you want to copy

' find the next open row in sheet2 using column A
rw = sh2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
' Now copy and clear the data
r1.Copy sh2.Cells(rw, "A")
' clears the value after it is transferred
r1.ClearContents
bk.Save
bk.Close SaveChanges:=False

End Sub

Answer
Gatlin,


this has been compiled without error.  There were some typos in the code.  


Private Sub CommandButton1_Click()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, rw As Long, bk As Workbook
Dim s As String, s1 As String

Set sh1 = Worksheets("Sheet1")

s = "C\:Users\mccongk\Desktop\Test2.xlsm"  '<= change
s1 = "Test2.xlsm"  '<== change
On Error Resume Next
Workbooks.Open s
On Error GoTo 0
Set bk = ActiveWorkbook

If InStr(1, bk.Name, s1, vbTextCompare) = 0 Then
MsgBox s1 & " was not opened - quitting"
Exit Sub
End If

Set sh2 = bk.Worksheets("Sheet1") '<= changed line

Set r1 = sh1.Range("A2:H2")  '<== change to the range you want to copy

' find the next open row in sheet2 using column A
rw = sh2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
' Now copy and clear the data
r1.Copy sh2.Cells(rw, "A")
' clears the value after it is transferred
r1.ClearContents
bk.Save
bk.Close SaveChanges:=False

End Sub


When macros start involving other workbooks and so forth, my ability to test to the code is limited - I don't have time to set up all the different situations - so the code is offered as is as an example for the user to implement/tweak.  Obviously when the code can be tested conveniently, I test it.  

Also be aware that the Allexperts software sometimes remove "\" characters.  For example, you code came over with this line:

s = "C:UsersmccongkDesktopTest2.xlsm"  '<= change

further note that the code assumes it is in the users workbook and being run from that workbook.  The line

Set sh1 = Worksheets("Sheet1")

sets sh1 to the worksheet with the data to be transferred - but since Worksheets is not qualified it will use the workbook with the commandbutton for sheet1 when the code is run.  

the fact that your master workbook has an .xlsm extension raises some questions in my assumptions, but just so you know.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.