Collecting data from one workbook to another

[Big Phred]

I have put together (my meager skill level prohibits me from using the word "developed") a workbook with multiple sheets with tables and formulas, the end result of which is a "Results" sheet that is a summary of data that has been entered and/or calculated through the process. What I would like to do is have a "button" or even a macro that will take the resultant column of results and copy it to another workbook (or at least another sheet) as a row - basically a poor man's database. I know there are many other programs including Access that are better suited for this, but due to my skill and my company's software "library", I am limited to Excel.

Here's a quick simulation - after making entries on a worksheet, another sheet crunches that information and produces a "Results" page like this:

Date 9/30/15
Product Widget
Size Large
Price 9.95
Buyer Joe's Plumbing

I want a macro or a "button" to "click" and have that information added to another sheet or book on the next available row as:

9/30/15 Widget Large 9.95 Joe's Plumbing

Then I can use the data table that is produced to analyze and massage data in groups.

Is there any way to do this in Excel??

 

[JackDanIce]

Here's the macro code to do what you need, you'll need to insert this into a module and then link a button to it (all easy to find with internet searches):

Sub Macro1()

Dim x as Long
Dim rng as Range

  'Assumes values you want to copy are on column B of sheet Results
  'And that data starts in cell B1
  With Sheets("Results")
      x = .Range("B" & .Rows.count).End(xlUp).Row
      Set rng = .Range("B1").resize(x)
  End With

  'Destination sheet, change name to suit
  With Sheets("Sheet1")
      x = .Range("A" & .Rows.Count).End(xlUp).Row
      .Range("A" & x).Resize(1,Rng.Rows.Count).Value = Application.Transpose(rng.Value)
  End With

  Set rng = Nothing

End Sub

 

[Big Phred]

Outstanding! Thanks JackDanIce.

Just two issues to fix:
The transfer of data is working fine, but additional uses overwrite the same row - I was hoping this would add a new row each time so I would build a database or archive sheet.
How do I make the results be "posted" to a sheet in a separate workbook?

Thank you so much for your help!

 

[JackDanIce]

You're welcome.

Here's the change to put the data into a new row (+1 part in blue, which I thought my code was doing but obviously, I forgot to include or missed out) with code for entering into a different workbook

Sub Macro1()

Dim x As Long
Dim rng As Range

Dim destWb  As Workbook

  'Assumes values you want to copy are on column B of sheet Results
  'And that data starts in cell B1
  With Sheets("Results")
      x = .Range("B" & .rows.Count).End(xlUp).row
      Set rng = .Range("B1").Resize(x)
  End With

  'Destination workbook, change file path to suit
  Set destWb = Workbooks.Open("C:\MyWork\OutputFile.xlsm")

  'Destination sheet, change name to suit
  With destWb.Sheets("Sheet1")
      x = .Range("A" & .rows.Count).End(xlUp).row + 1
      .Range("A" & x).Resize(1, rng.rows.Count).value = Application.Transpose(rng.value)
  End With
  
  If Msgbox("Close destination workbook?", vbYesNo) = vbYes Then
      destWb.Close True
  End If

  ThisWorkbook.Activate

  Set rng = Nothing
  Set destWb = Nothing
  
End Sub

 

[Big Phred]

Perfect! Thanks again for all of your expert help, JackDanIce!!

 

[JackDanIce]

No problem and glad you have it working now.

 

[UWMmakow]

JackDanIce, I'm curious, why count the number of rows in the workbook and go up from the bottom rather than starting from the top and going down? What's everyone's preference/best practice on this?

I understand that going top down runs the risk of skipping to the bottom of the sheet and crashing if there's no value under the starting row.

What I'm wondering is, if you count the rows all the way to the bottom, does that throw off the used range and the scroll space of the sheet?