+ Reply to Thread
Results 1 to 17 of 17

Macro to copy date to specific sheet based on data from another sheet.

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    01-21-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    258

    Re: Macro to copy date to specific sheet based on data from another sheet.

    thanks for replying AB33. Ignore the destRows, they are for a different function and that one works fine.

    It's 'testRow' that I'm trying to sort out. and it's selecting the correct row, it's just not incrementing properly. Taking out the
    testRow =  testRow + 1
    just screws everything up.

  2. #2
    Forum Contributor
    Join Date
    01-21-2013
    Location
    Aberdeen, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    258

    Re: Macro to copy date to specific sheet based on data from another sheet.

    Correction, I am that someone. Even if that someone used trial and error for hours to figure it out.
    Don't know why anyone else would ever need/use this overly complicated code but here's it is.

    Sub PrintPDF()
       Dim FileName As String
       Dim trSh As Worksheet
       Dim trRegSh As Worksheet
       Dim Docsht As Worksheet
       Dim destRow As Long
       Dim testRow As Long
       Dim Nametest As Worksheet
       Dim n As Integer, m As Integer
       Dim r As Integer, i As Integer
       Dim myRecipients As String
       Dim FPath As String
       Dim NameEase As String
       
       FPath = "C:\Users\rthomson\Desktop\"
       
       With ThisWorkbook
          Set trSh = .Sheets("Transmittal Sheet")
          Set trRegSh = .Sheets("Transmittal Register")
       End With
    
       'save as pdf
       FileName = trSh.Cells(12, "R")
       trSh.ExportAsFixedFormat xlTypePDF, FileName:= _
       FPath & FileName & ".pdf"
    
       'move data from transmittal sheet to transmittal register
       destRow = trRegSh.Cells(Rows.Count, 1).End(xlUp).Row
       For i = 12 To 15
          'for each recipient
          If Trim(trSh.Cells(i, "c")) = "" Then
             Exit For
          Else
             If i > 12 Then
                myRecipients = myRecipients & "; "
             End If
             myRecipients = myRecipients & trSh.Cells(i, "c")
          End If
       Next i
       
       For r = 26 To 33
          'for each document
          If Trim(trSh.Cells(r, "h")) = "" Then
             Exit For
          End If
            NameEase = trSh.Cells(r, "h")
            Set Nametest = Sheets(NameEase)
          testRow = Nametest.Range("B30").End(xlUp).Row
          destRow = destRow + 1
          testRow = testRow + 1
          trRegSh.Cells(destRow, "b") = trSh.Cells(r, "f")
          trRegSh.Cells(destRow, "c") = trSh.Cells(r, "h")
          trRegSh.Cells(destRow, "d") = trSh.Cells(r, "j")
          trRegSh.Cells(destRow, "e") = "DCC"
          trRegSh.Cells(destRow, "f") = myRecipients
          trRegSh.Cells(destRow, "g") = trSh.Cells(39, "R")
          trRegSh.Cells(destRow, "h") = Date
          trRegSh.Cells(destRow, "i") = trSh.Range("r40")
          trRegSh.Hyperlinks.Add Anchor:=trRegSh.Cells(destRow, "a"), _
          Address:=FPath & FileName & ".pdf", _
          ScreenTip:="Click to open Transmittal", _
          TextToDisplay:=FileName
          Nametest.Cells(testRow, "B") = trSh.Cells(12, "R")
          Nametest.Cells(testRow, "E") = trSh.Cells(r, "f")
          Nametest.Cells(testRow, "F") = myRecipients
          Nametest.Cells(testRow, "K") = trSh.Cells(39, "R")
          Nametest.Cells(testRow, "N") = Date
       Next r
       Set trSh = Nothing
       Set trRegSh = Nothing
    End Sub
    Now if only I could give myself rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need VBA to auto copy data from one sheet to specific on based of Staus
    By nadeemjadoon in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-04-2013, 12:25 PM
  2. HELP! Macro to Copy specific cells from one sheet to another based on specific criteria
    By atriscritti in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-11-2012, 11:05 AM
  3. [SOLVED] Copy and paste data from sheet 2 to sheet 1 based on specific criteria on sheet 1
    By VBADUD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2012, 04:18 AM
  4. [SOLVED] Macro to Copy Data from one Sheet A to Sheet B based on value in cell on sheet A
    By scass in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-11-2012, 07:21 PM
  5. Copy data from sheet 1 to sheet 2 based on day/date
    By jonpdavies@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-01-2005, 12:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1