Solved: Send Data to different workbook

[igorski88]

Im sending data to to a different Workbook. the workBook will be a basic electronic LogBook of transactions. I have it working with one minor issue. The first column is shifted down one row. "A1" is giving me all my problems. Please help me with this minor problem. I have been going at this for hours!! someone... BE MY HERO!!!

Sub Transfer_Data()Dim ThisWB As Variant
Dim LogBookName As Variant
Dim Lastrow As Long
ThisWB = ThisWorkbook.Name
Set LogBook = Workbooks.Open(fn)
LogBookName = LogBook.Name
With Workbooks(LogBookName).Sheets("sheet1")
'Find the last row used
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
MsgBox Lastrow
'IGOR: heading and data will be on the same row.... need to seperat the rows
'Populat Heading
If checkHeading = True Then
'Date Recorded 'Heading
If check1 = True Then
If .Range("a" & Lastrow).Offset(1, 0).Value = "" Then
.Range("a" & Lastrow).Offset(1, 0).Value = "Date Recorded:"
Else
.Range("Z" & Lastrow).End(xlToLeft).Offset(0, 1).Value = "Date Recorded:"
End If
End If 'Date Recorded
'Type of Transaction 'Heading
If check2 = True Then
If .Range("a" & Lastrow).Offset(1, 0).Value = "" Then
.Range("a" & Lastrow).Offset(1, 0).Value = "Type of Transaction:"
Else
.Range("Z" & Lastrow).End(xlToLeft).Offset(0, 1).Value = "Type of Transaction:"
End If
End If 'Type of Transaction
'Date of Transaction 'Heading
If check3 = True Then
If .Range("a" & Lastrow).Offset(1, 0).Value = "" Then
.Range("a" & Lastrow).Offset(1, 0).Value = "Date of Transaction:"
Else
.Range("Z" & Lastrow).End(xlToLeft).Offset(0, 1).Value = "Date of Transaction:"
End If
End If 'Date of Transaction
End If '''''''''Heading
'Find the last row used again
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
MsgBox Lastrow
'Date Recorded
If check1 = True Then
If .Range("a" & Lastrow).Offset(1, 0).Value = "" Then
.Range("a" & Lastrow).Offset(1, 0).Value = Date
Else
.Range("Z" & Lastrow).End(xlToLeft).Offset(0, 1).Value = Date
End If
End If 'Date Recorded
'Type of Transaction
If check2 = True Then
If .Range("a" & Lastrow).Offset(1, 0).Value = "" Then
.Range("a" & Lastrow).Offset(1, 0).Value = Sheet2.Range("AB7").Value & "/" & Sheet2.Range("B6").Value
Else
.Range("Z" & Lastrow).End(xlToLeft).Offset(0, 1).Value = Sheet2.Range("AB7").Value & "/" & Sheet2.Range("B6").Value
End If
End If 'Type of Transaction
'Date of Transaction
If check3 = True Then
If .Range("a" & Lastrow).Offset(1, 0).Value = "" Then
.Range("a" & Lastrow).Offset(1, 0).Value = Sheet2.TextBox13.Text
Else
.Range("Z" & Lastrow).End(xlToLeft).Offset(0, 1).Value = Sheet2.TextBox13.Text
End If
End If 'Date of Transaction
'AUTO fIT Colomns
.Range("A1:Z2").Columns.Select 'autofit requires columns to be selected
.Range("A1:Z2").Columns.AutoFit
End With
'Lastly save and close Logbook
LogBook.Save
LogBook.Close
End Sub

[NoSparks]

maybe try something like this

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

If LastRow = 1 Then 'put the headings on row 1
.Range("a" & LastRow).Value = "Date Recorded:"
.Range("a" & LastRow).Offset(0, 1).Value = "Type of Transaction:"
.Range("a" & LastRow).Offset(0, 2).Value = "Date of Transaction:"
Else 'put the data on the next row
.Range("a" & LastRow).Offset(1, 0).Value = Date
.Range("a" & LastRow).Offset(1, 1).Value = Sheet2.Range("AB7").Value & "/" & Sheet2.Range("B6").Value
.Range("a" & LastRow).Offset(1, 2).Value = Sheet2.TextBox13.Text
End If

[igorski88]

Thanks for the response. Unfortunately your method did not work. Here's what I did and it worked for me.
Sub Transfer_Data()
Dim ThisWB As Variant
Dim LogBookName As Variant
Dim LastRow As Long

ThisWB = ThisWorkbook.Name

Set LogBook = Workbooks.Open(fn)

LogBookName = LogBook.Name


With Workbooks(LogBookName).Sheets("sheet1")

'Find the last row used
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastRow = LastRow + 1


MsgBox "LastRow: " & LastRow

'Populat Heading
If checkHeading = True Then

'Date Recorded 'Heading
If check1 = True Then
If .Range("a1").Value = "" Then
.Range("a1").Value = "Date Recorded:"
LastRow = 1
Else
If .Range("Z" & LastRow).End(xlToLeft).Value = "" Then
.Range("Z" & LastRow).End(xlToLeft).Value = "Date Recorded:"
Else
.Range("Z" & LastRow).End(xlToLeft).Offset(0, 1).Value = "Date Recorded:"
End If
End If
End If 'Date Recorded

'Type of Transaction 'Heading
If check2 = True Then
If .Range("a1").Value = "" Then '
.Range("a1").Value = "Type of Transaction:"
LastRow = 1
Else
If .Range("Z" & LastRow).End(xlToLeft).Value = "" Then
.Range("Z" & LastRow).End(xlToLeft).Value = "Type of Transaction:"
Else
.Range("Z" & LastRow).End(xlToLeft).Offset(0, 1).Value = "Type of Transaction:"
End If
End If
End If '"Type of Transaction:"

'Date of Transaction 'Heading "Date of Transaction:"
If check3 = True Then
If .Range("a1").Value = "" Then '
.Range("a1").Value = "Date of Transaction:"
LastRow = 1
Else
If .Range("Z" & LastRow).End(xlToLeft).Value = "" Then
.Range("Z" & LastRow).End(xlToLeft).Value = "Date of Transaction:"
Else
.Range("Z" & LastRow).End(xlToLeft).Offset(0, 1).Value = "Date of Transaction:"
End If
End If
End If '"Date of Transaction:"