+ Reply to Thread
Results 1 to 4 of 4

loop to copy/paste data between worksheets, save results

  1. #1
    Registered User
    Join Date
    06-27-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    5

    Question loop to copy/paste data between worksheets, save results

    VBA beginner here, thanks for helping.

    What we are essentially trying to accomplish is creating a form-like table (Sheet 1) for EACH row (n=5000+) of Sheet 2, and saving each result as a .pdf.

    So I am trying to write a macro to:

    -Copy data from select cells of a single row of Sheet 2
    -Paste data to select cells (different range than Sheet 2) of Sheet 1
    -Save Sheet 1 as .pdf with cell A3 value as filename
    -Move to next sequential row of Sheet 2 and repeat process to last row.

    I've written the part to save as .pdf and it works, but need help with the rest.

    Below are listed the Sheet 1 cells with the Sheet 2 columns that need to be copied FROM for each row.
    Also have attached a simplified version of the workbook (Sheet 1 cells currently have formulas for Sheet 2 values, which is how we have semi-manually been creating the Sheet 1 results). macro_test.xlsm

    If someone could get me started in the right direction with an approach or piece of sample code that would be great!

    [Sheet 1 CELL] = [Sheet 2 COLUMN]
    A3 = F
    D3 = J
    F3 = K
    A6 = H
    D6 = B
    F6 = BN
    A9 = G
    A12 = L
    A21 = X
    B21 = Y
    C21 = Z
    D21 = AA
    E21 = AB
    F21 = AC
    G21 = AD
    A25 = AE
    B25 = AF
    C25 = AG
    D25 = AH
    E25 = AI
    F25 = AJ
    G25 = AK
    A29 = AL
    B29 = AM
    C29 = AN
    D29 = AO
    E29 = AP
    F29 = AQ
    G29 = AR
    A33 = AS
    B33 = AT
    C33 = AU
    D33 = AV
    E33 = AW
    F33 = AX
    G33 = AY
    A37 = AZ
    A43 = BG
    B43 = BH
    C43 = BI
    D43 = BJ
    E43 = BK
    F43 = BL
    A47 = BM

  2. #2
    Valued Forum Contributor
    Join Date
    11-20-2012
    Location
    Seattle, WA USA
    MS-Off Ver
    Excel 2010
    Posts
    597

    Re: loop to copy/paste data between worksheets, save results

    this should work, make sure there aren't any typos, i'm attaching back the workbook that I ran to test the last row, so all data in Sheet1 will correspond to Sheet2 row 11. Sheet1 (2) was the original data that contained data from Sheet2 row 2

    macro_test.xlsm

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-27-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: loop to copy/paste data between worksheets, save results

    Thank you so much! I am testing this, will update.

  4. #4
    Registered User
    Join Date
    06-27-2013
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    5

    Thumbs up [SOLVED] Re: loop to copy/paste data between worksheets, save results

    This code works like a charm!

    The only minor change I had to make was in this string (change in bold):

    Sheets("Sheet1").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "N:\Nevada Rocks\Samples\" & Range("F" & Lrow).Text & ".pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    ...In order to name each .pdf with a unique identifier from Column F/Sheet2 (which I didn't explain clearly in original post).


    Many thanks for helping me solve this and get introduced to VBA, it was immensely helpful! I learned a ton that I doubt I would have if I had attempted thrashing it out on my own without this example and this forum. Hope it might come in use for someone else too (the slightly revised full code is below). THANK YOU!


    Sub fillsht1()

    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim sht1 As Worksheet

    Set sht1 = Sheets("Sheet1")
    With Sheets("Sheet2")

    .Select

    Firstrow = 2
    Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

    For Lrow = Firstrow To Lastrow Step 1

    sht1.Range("A3").Value = .Range("F" & Lrow).Value
    sht1.Range("D3").Value = .Range("J" & Lrow).Value
    sht1.Range("F3").Value = .Range("K" & Lrow).Value
    sht1.Range("A6").Value = .Range("H" & Lrow).Value
    sht1.Range("D6").Value = .Range("BX" & Lrow).Value
    sht1.Range("F6").Value = .Range("BN" & Lrow).Value
    sht1.Range("A9").Value = .Range("G" & Lrow).Value
    sht1.Range("A12").Value = .Range("L" & Lrow).Value
    sht1.Range("A21").Value = .Range("X" & Lrow).Value
    sht1.Range("B21").Value = .Range("Y" & Lrow).Value
    sht1.Range("C21").Value = .Range("Z" & Lrow).Value
    sht1.Range("D21").Value = .Range("AA" & Lrow).Value
    sht1.Range("E21").Value = .Range("AB" & Lrow).Value
    sht1.Range("F21").Value = .Range("AC" & Lrow).Value
    sht1.Range("G21").Value = .Range("AD" & Lrow).Value
    sht1.Range("A25").Value = .Range("AE" & Lrow).Value
    sht1.Range("B25").Value = .Range("AF" & Lrow).Value
    sht1.Range("C25").Value = .Range("AG" & Lrow).Value
    sht1.Range("D25").Value = .Range("AH" & Lrow).Value
    sht1.Range("E25").Value = .Range("AI" & Lrow).Value
    sht1.Range("F25").Value = .Range("AJ" & Lrow).Value
    sht1.Range("G25").Value = .Range("AK" & Lrow).Value
    sht1.Range("A29").Value = .Range("AL" & Lrow).Value
    sht1.Range("B29").Value = .Range("AM" & Lrow).Value
    sht1.Range("C29").Value = .Range("AN" & Lrow).Value
    sht1.Range("D29").Value = .Range("AO" & Lrow).Value
    sht1.Range("E29").Value = .Range("AP" & Lrow).Value
    sht1.Range("F29").Value = .Range("AQ" & Lrow).Value
    sht1.Range("G29").Value = .Range("AR" & Lrow).Value
    sht1.Range("A33").Value = .Range("AS" & Lrow).Value
    sht1.Range("B33").Value = .Range("AT" & Lrow).Value
    sht1.Range("C33").Value = .Range("AU" & Lrow).Value
    sht1.Range("D33").Value = .Range("AV" & Lrow).Value
    sht1.Range("E33").Value = .Range("AW" & Lrow).Value
    sht1.Range("F33").Value = .Range("AX" & Lrow).Value
    sht1.Range("G33").Value = .Range("AY" & Lrow).Value
    sht1.Range("A37").Value = .Range("AZ" & Lrow).Value
    sht1.Range("A43").Value = .Range("BG" & Lrow).Value
    sht1.Range("B43").Value = .Range("BH" & Lrow).Value
    sht1.Range("C43").Value = .Range("BI" & Lrow).Value
    sht1.Range("D43").Value = .Range("BJ" & Lrow).Value
    sht1.Range("E43").Value = .Range("BK" & Lrow).Value
    sht1.Range("F43").Value = .Range("BL" & Lrow).Value
    sht1.Range("A47").Value = .Range("BM" & Lrow).Value

    Sheets("Sheet1").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "N:\Nevada Rocks\Samples\" & Range("F" & Lrow).Text & ".pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    Next Lrow

    End With




    End Sub
    Last edited by nbmg1; 07-09-2013 at 06:23 PM. Reason: code error

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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