Results 1 to 3 of 3

Thread: How to copy info sheet to sheet into different columns.

  1. #1

    How to copy info sheet to sheet into different columns.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    Firstly, would like to say hello, as I am new here. I have a button with a code that takes info from a number of cells in sheet1 and puts them into row in sheet2. It then saves sheet1 as a .pdf (this is being used as an invoice that is reused several times) based on new data I have entered each use. It works great for this. The problem I am having, is that I am pulling to much info from sheet1 and I am all the way to column 'J' with some cells being 27 wide. So what I am trying to do is get 3 of the copied cells to automatically go to sheet2 2 rows down. So far I can not figure out how to make the data change rows, only columns. Here is the code I am using...


    Sub LogInvoice_Click()
    Dim myFileName As String, lastRow As Integer
    myFileName = "B:\INVOICES\" & Sheets("Invoice").Range("C9") & "_" _
    & Format(Now(), "mm-dd-2014") & ".pdf"
    lastRow = Sheets("Ledger").UsedRange.SpecialCells(xlCellTypeLastCell).Row + 2
    'Transfer data to log:
    Sheets("Ledger").Cells(lastRow, 1) = Sheets("Invoice").Range("C9")
    Sheets("Ledger").Cells(lastRow, 2) = Sheets("Invoice").Range("C13")
    Sheets("Ledger").Cells(lastRow, 3) = Sheets("Invoice").Range("C22")
    Sheets("Ledger").Cells(lastRow, 4) = Sheets("Invoice").Range("B21")
    Sheets("Ledger").Cells(lastRow, 5) = Sheets("Invoice").Range("H21")
    Sheets("Ledger").Cells(lastRow, 6) = Sheets("Invoice").Range("J32")
    Sheets("Ledger").Cells(lastRow, 7) = Sheets("Invoice").Range("I33")
    Sheets("Ledger").Cells(lastRow, 8) = Sheets("Invoice").Range("B41")
    Sheets("Ledger").Cells(lastRow, 9) = Sheets("Invoice").Range("C36")
    Sheets("Ledger").Hyperlinks.Add Anchor:=Sheets("Ledger").Cells(lastRow, 10), Address:=myFileName, TextToDisplay:="Hard Copy"
    'Create pdf invoice:
    Sheets("Invoice").ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFileName
    End Sub

    Any help would be much appreciated!

    Thanks!!

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    I read it once and I think I know what you want then I read more and go WHAT???....

    so you want your data to be more condensed ie 3x3 grid instead of 1 x9?
    using your code


    Quote Originally Posted by Texety View Post
    Hello,

    Firstly, would like to say hello, as I am new here. I have a button with a code that takes info from a number of cells in sheet1 and puts them into row in sheet2. It then saves sheet1 as a .pdf (this is being used as an invoice that is reused several times) based on new data I have entered each use. It works great for this. The problem I am having, is that I am pulling to much info from sheet1 and I am all the way to column 'J' with some cells being 27 wide. So what I am trying to do is get 3 of the copied cells to automatically go to sheet2 2 rows down. So far I can not figure out how to make the data change rows, only columns. Here is the code I am using...


    Sub LogInvoice_Click()
    Dim myFileName As String, lastRow As Integer
    myFileName = "B:\INVOICES\" & Sheets("Invoice").Range("C9") & "_" _
    & Format(Now(), "mm-dd-2014") & ".pdf"
    lastRow = Sheets("Ledger").UsedRange.SpecialCells(xlCellTypeLastCell).Row + 2
    'Transfer data to log:
    Sheets("Ledger").Cells(lastRow, 1) = Sheets("Invoice").Range("C9")
    Sheets("Ledger").Cells(lastRow, 2) = Sheets("Invoice").Range("C13")
    Sheets("Ledger").Cells(lastRow, 3) = Sheets("Invoice").Range("C22")
    Sheets("Ledger").Cells(lastRow + 1, 1) = Sheets("Invoice").Range("B21")
    Sheets("Ledger").Cells(lastRow + 1, 2) = Sheets("Invoice").Range("H21")
    Sheets("Ledger").Cells(lastRow + 1, 3) = Sheets("Invoice").Range("J32")
    Sheets("Ledger").Cells(lastRow + 2, 1) = Sheets("Invoice").Range("I33")
    Sheets("Ledger").Cells(lastRow + 2, 2) = Sheets("Invoice").Range("B41")
    Sheets("Ledger").Cells(lastRow + 2, 3) = Sheets("Invoice").Range("C36")
    Sheets("Ledger").Hyperlinks.Add Anchor:=Sheets("Ledger").Cells(lastRow+2, 10), Address:=myFileName, TextToDisplay:="Hard Copy"
    'Create pdf invoice:
    Sheets("Invoice").ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFileName
    End Sub

    Any help would be much appreciated!

    Thanks!!

  3. #3
    Sorry about that. I have been doing nothing but staring at Excel and VBA for days. Basically I was trying to get data entered in in multiple rows, before the data was only going to 1 row per entry. I hope this explains a little better!

    Anyways, I tried your code, and it does half and half what I wanted. It gives me the idea of how to expand data to more then one row, so with a little editing, I should be able to make it work!

    Thanks for your help!

Posting Permissions

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