Set Source and Destination in VBA

[posfog]

Hi All

I have a macro that allows me to move information from one sheet to another in the same workbook, but i require it to move to a different work book using the following bit of code

' Set source and destination sheets
Set src = Sheets("TOUCH SCREEN")
Set dst = Sheets("RAW DATA")

How would i need to change this if i want the destination to be a different workbook and sheet name?


Thanks in advance

P

 

[posfog]

.

 

[Ryan1996]

Not that i'm any good with this stuff a quick google gives me the below result:

Sub Open_Workbook()

Dim nb As Workbook, tw As Workbook, ts As Worksheet
a = Application.GetOpenFilename
If a = False Or IsEmpty(a) Then Exit Sub
With Application
.ScreenUpdating = False
End With
Set tw = ThisWorkbook
Set ts = tw.ActiveSheet
Set nb = Workbooks.Open(a)
nb.Sheets("Sheet1").Copy After:=Workbooks("file name.xlsm").Sheets("Pivot Table")

End Sub

Whether you need all of the above information i doubt it but the relevant information you will need is to copy "sheet1" into the worksheet of "name" with the sheet name being "name"
Hopefully this helps you a little more?
If you needed a more dynamic approach with name changing files then i'm also stuck.

 

[gallen]

Assuming the source workbook is open...

Change the workbook name accordingly

Set src = Workbooks("WORKBOOKNAME").Worksheets("TOUCHSCREEN")

If it isn't open you will need to open it first either manually or in code

 

[Ryan1996]

i want to delete this post but i dont know how to?

Didn't see this message before i posted, i believe this can be done within the settings of the post. Think there is a button within the edit layout.

 

[posfog]

Assuming the source workbook is open...

Change the workbook name accordingly

Set src = Workbooks("WORKBOOKNAME").Worksheets("TOUCHSCREEN")

If it isn't open you will need to open it first either manually or in code

What if the work book in question is closed, would it still send the details to it and would it save the file?


I would put the whole VBA code in here but its very lengthy

 

[gallen]

No. You'd have to open the file with something like:

    Dim srcWB As Workbook
    Set srcWB = Workbooks.Open("Yourpath/Yourworkbookname")
    
    Set src = srcWB.Worksheets("TOUCHSCREEN")

There is a lot of code out there that shows you more proficient methods that let the user select the file etc...