How to copy info sheet to sheet into different columns.

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!!

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

Texety said:

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!!

Click to expand...

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!