VBA - copying from one workbook to another

Barbarte

Board Regular
Joined
Dec 13, 2006
Messages
125
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:

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:

Code:
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

Board Regular
Joined
Dec 13, 2006
Messages
125
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!!!
 

Forum statistics

Threads
1,081,561
Messages
5,359,611
Members
400,540
Latest member
JimUSMC

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top