Help needed with Macro to send data to another sheet/workbook

  • Thread starter Thread starter Alan T
  • Start date Start date
A

Alan T

The spreadsheet I've created makes the user select various options from
different drop down menus... once selected the data is displayed on
another part of the same sheet. I have a macro in place to clear these
cells when the button is selected, I will still be looking to run this
macro also.

I'm looking for help to create a macro that will allow this data to be
transferred onto another workbook/sheet on a button click. The export
of data must not overwirte the existing data on the other
workbook/sheet and should start on a new line each time the button is
selected.

Anyone got any suggestions?
 
No knowing all the details of your application I use the following
convenient assumptions:

Data to to sent is in "Book1", "Sheet1", in a range called
"RangeToCopy".

I will be sent to "Book2", "Sheet1", in successive rows, starting in
cell "A1".

You have to create a range named "StartingRow" in "Sheet1" of "Book1".
It keeps track, via the macro, of how many rows have been used in the
destination book. Put the value 1 in it initially, to show that the 1st
row in "Book2", "Sheet1" is free. It increments by the number of rows
of data copied each time.

Put this macro in "Book1". Hopefully the variable names are
self-explanatory.


Sub Macro1()

Dim WhereToPaste As String
Dim RowsInRangeToCopy As Integer

RowsInRangeToCopy = Worksheets("Sheet1").Range("RangeToCopy"). _
Rows.Count
WhereToPaste = Worksheets("Sheet1").Range("StartingRow").Value
Worksheets("Sheet1").Range("RangeToCopy").Copy
Workbooks("Book2").Worksheets("Sheet1"). _
Cells(WhereToPaste, 1).PasteSpecial
With Worksheets("Sheet1").Range("StartingRow")
Value = .Value + RowsInRangeToCopy
End With
End Sub

Good luck!
 
Brandenkopf said:
*No knowing all the details of your application I use the following
convenient assumptions:

Data to to sent is in "Book1", "Sheet1", in a range called
"RangeToCopy".

I will be sent to "Book2", "Sheet1", in successive rows, starting in
cell "A1".

You have to create a range named "StartingRow" in "Sheet1" of
"Book1". It keeps track, via the macro, of how many rows have been
used in the destination book. Put the value 1 in it initially, to
show that the 1st row in "Book2", "Sheet1" is free. It increments by
the number of rows of data copied each time.

Put this macro in "Book1". Hopefully the variable names are
self-explanatory.


Sub Macro1()

Dim WhereToPaste As String
Dim RowsInRangeToCopy As Integer

RowsInRangeToCopy = Worksheets("Sheet1").Range("RangeToCopy"). _
Rows.Count
WhereToPaste = Worksheets("Sheet1").Range("StartingRow").Value
Worksheets("Sheet1").Range("RangeToCopy").Copy
-Workbooks("Book2").Worksheets("Sheet1"). _
Cells(WhereToPaste, 1).PasteSpecial-
With Worksheets("Sheet1").Range("StartingRow")
.Value = .Value + RowsInRangeToCopy
End With
End Sub

Good luck! *
Click to expand...

I'm struggling to get the above to work. I get
"Run-Time Error '13'
Type Mismatch"
error message. I recorded some more details about what I was attempting
to do below.

I've tried to simplify what I am doing and moved all the data onto one
book, but using separate sheets (CLT and CS) on the book. I'm looking
to transfer data from cells L8,L10,L12,L14 on sheet CLT to sheet CS.
The data should record on Sheet CS in Cells A2,C2,E2,G2, then
A3,C3,E3,G3 etc
 
Another way might be something like this:

Option Explicit
Sub testme01()

Dim RngToCopy As Range
Dim RngToPaste As Range

With Workbooks("book1.xls").Worksheets("sheet1")
Set RngToCopy _
= .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 8)
End With

With Workbooks("book2.xls").Worksheets("sheet1")
Set RngToPaste = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

RngToCopy.Copy _
Destination:=RngToPaste

End Sub

I copied from book1.xls, sheet1. I started in A1 and copied until the end of
data in column A. Then I grabbed 8 columns (A1:H???) (that's the resize(,8)
stuff.) (Modify to suit.)

Then I found the next available cell in column A of book2.xls, sheet1 and pasted
there.
 
Back
Top