VBA - copying from one workbook to another

[Barbarte]

Hi everyone,

I'm hoping you can help me with this.
I've written some code to help me automate some repetitive processes.
there are two macros in particular that I need working, one copies data from workbook1, goes to workbook2, inserts a row and pastes the data to the new row, then saves workbook1 and adds a hyperlink in workbook2 linking to workbook1 here's the code:

Sub Save_and_list()

Dim Co As String
Dim Contact As String
Dim Project As String
Dim Phone As String
Dim Email As String
Dim Date1 As String
Dim Description As String
Dim ClearanceType As String
Dim Link As String


Dim rowno1 As Long

rowno1 = 5

Co = Cells(1, 2).Value
Contact = Cells(2, 2).Value
Project = Cells(3, 2).Value
Phone = Cells(1, 4).Value
Email = Cells(2, 4).Value
Date1 = Cells(3, 4).Value
Description = Cells(2, 6).Value
ClearanceType = Cells(3, 6).Value
Link = "H:\Administration\Budget\General Licensing Summary\Sync Licensing Applications\"


Windows("General Licensing Summary 2009.xlsm").Activate
    Sheets("Other Sync Licensing").Activate
    
    Rows(rowno1).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow
    

    Cells(rowno1, 2).Value = Co
    Cells(rowno1, 3).Value = Project
    Cells(rowno1, 4).Value = Contact
    Cells(rowno1, 5).Value = Phone
    Cells(rowno1, 6).Value = Email
    Cells(rowno1, 1).Value = Date1
    Cells(rowno1, 9).Value = Description
    Cells(rowno1, 8).Value = ClearanceType
    
    Cells(rowno1, 7).Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Link & Co & " - " & Project & ".xlsm", TextToDisplay:="***"

Windows("Blank Sync Progress Report.xlsm").Activate

ActiveWorkbook.SaveAs Filename:= _
Link & Co & " - " & Project & ".xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

The second macro looks at the same original (workbook1), copies some data from it to workbook3 then saves the data in a similar manour to the one above.

However, I'm using very similar code, but the macro keeps pasting the data in workbook1.
Anyone got any ideas?
the code for the second macro is as follows:

Sub Mac_Sync_Request_Spooler()

Windows("Blank Sync Progress Report.xlsm").Activate

Dim rowno1 As Long
rowno1 = 6


Dim Publisher As String
Dim Req As Integer
Dim TrackTitle As String
Dim Composer As String
Dim Share As Long
Dim Duration As String
Dim CompanyName As String
Dim ProjectTitle As String
Dim MediaFormat As String
Dim ProjectSynopsis As String
Dim SceneDescription As String
Dim Terms As String
Dim Rate As Currency
Dim Link As String


Publisher = Cells(rowno1, 6)
Req = Cells(rowno1, 14)
TrackTitle = Cells(rowno1, 4)
Composer = Cells(rowno1, 5)
Share = Cells(rowno1, 9)
Duration = Cells(rowno1, 6)
CompanyName = Cells(1, 2)
ProjectTitle = Cells(3, 2)
MediaFormat = Cells(9, 3)
ProjectSynopsis = Cells(2, 6)
SceneDescription = Cells(rowno1, 2)
Terms = Cells(rowno1, 13)
Rate = Cells(rowno1, 10)

Windows("Blank Pub Sync Request.xlsx").Activate

Cells(6, 3).Value = Publisher
Cells(13, 3).Value = Publisher
Cells(8, 3).Value = Req
Cells(11, 3).Value = TrackTitle
Cells(12, 3).Value = Composer
Cells(14, 3).Value = Share
Cells(15, 3).Value = Duration
Cells(17, 3).Value = CompanyName
Cells(18, 3).Value = ProjectTitle
Cells(19, 3).Value = MediaFormat
Cells(20, 3).Value = ProjectSynopsis
Cells(21, 3).Value = SceneDescription
Cells(22, 3).Value = Terms
Cells(23, 3).Value = Rate

As far as I can see there is no reason this shouldn't put the data in the correct cells in workbook3, yet it adds it to workbook1.

Many thanks for your help.

Regards,

Paul.

 

[Barbarte]

Re: VBA - copying from one workbook to another [Solved]

just read an old post of mine and figured it out.
I had the exact problem again, code in the button instead of the spreadsheet.

DOH!!!