I have a workbook with two sheets: 'Sheet A' and 'Sheet B'. 'Sheet B'
holds checkboxes with which to enter responses and the responses are
aggregated into A83:AS83.

I need to use a button to copy A83:AS83 and paste it into the first
empty row below row 500 in 'Sheet A'. As the process would be repeated
more than a few times, the number of the row into which A83:AS83 would
be pasted would need to increment automatically.

I would be grateful for any pointers on how I might be able to do this!

TIA

--
F

I will assume you currently have data in A500 or beyond on Sheet A

This macro will paste to first blank row and increment each time it is run.

Sub findbottom_paste()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Worksheets("Sheet B").Cells(Rows.Count, 1).End(xlUp) _
.Range("A83:AS83")
Set rng2 = Worksheets("Sheet A").Cells(Rows.Count, 1).End(xlUp) _
..Offset(1, 0)
rng1.Copy Destination:=rng2
End Sub


Gord Dibben MS Excel MVP

On Sun, 01 May 2011 16:53:36 +0100, F <news@nowhere wrote:

I have a workbook with two sheets: 'Sheet A' and 'Sheet B'. 'Sheet B'
holds checkboxes with which to enter responses and the responses are
aggregated into A83:AS83.

I need to use a button to copy A83:AS83 and paste it into the first
empty row below row 500 in 'Sheet A'. As the process would be repeated
more than a few times, the number of the row into which A83:AS83 would
be pasted would need to increment automatically.

I would be grateful for any pointers on how I might be able to do this!

TIA

Hi

Thanks for that, but I'm afraid it's not pasting anything. There are no
error messages, just no discernible action.

I had been trying to get something working by recording a macro that did
part of the job: copy and paste line 83, but I'm getting 'Runtime
error: type mismatch' when I try to automate where the paste should be.

Below, 'Transfer', is what I've got so far, and if I replace the line
highlighted in the error:
'Rows("nextrow:nextrow").Select' with
'Rows("501:501").Select' it works. I see the row transferred and cell
A90 increments. I can't, however, find what is causing the problem.

Sub Transfer()
'
' Transfer Macro
'

'
Dim nextrow As Range
Set nextrow = Worksheets("Sheet B").Range("A90")
Rows("83:83").Select
Selection.Copy
Sheets("Sheet A").Select
Rows("nextrow:nextrow").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
nextrow.Value = nextrow.Value + 1
End Sub

--
F



On 01/05/2011 18:33 Gord Dibben wrote:

I will assume you currently have data in A500 or beyond on Sheet A

This macro will paste to first blank row and increment each time it is run.

Sub findbottom_paste()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Worksheets("Sheet B").Cells(Rows.Count, 1).End(xlUp) _
.Range("A83:AS83")
Set rng2 = Worksheets("Sheet A").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
rng1.Copy Destination:=rng2
End Sub


Gord Dibben MS Excel MVP

On Sun, 01 May 2011 16:53:36 +0100, F<news@nowhere wrote:

I have a workbook with two sheets: 'Sheet A' and 'Sheet B'. 'Sheet B'
holds checkboxes with which to enter responses and the responses are
aggregated into A83:AS83.

I need to use a button to copy A83:AS83 and paste it into the first
empty row below row 500 in 'Sheet A'. As the process would be repeated
more than a few times, the number of the row into which A83:AS83 would
be pasted would need to increment automatically.

I would be grateful for any pointers on how I might be able to do this!

TIA

More typos......bad day today.

Set rng1 = Worksheets("Sheet B").Cells(Rows.Count, 1).End(xlUp) _
.Range("A83:AS83")

Change to.......

Set rng1 = Worksheets("Sheet B").Range("A83:AS83")


Gord

On Sun, 01 May 2011 19:51:09 +0100, F <news@nowhere wrote:

Hi

Thanks for that, but I'm afraid it's not pasting anything. There are no
error messages, just no discernible action.

I had been trying to get something working by recording a macro that did
part of the job: copy and paste line 83, but I'm getting 'Runtime
error: type mismatch' when I try to automate where the paste should be.

Below, 'Transfer', is what I've got so far, and if I replace the line
highlighted in the error:
'Rows("nextrow:nextrow").Select' with
'Rows("501:501").Select' it works. I see the row transferred and cell
A90 increments. I can't, however, find what is causing the problem.

Sub Transfer()
'
' Transfer Macro
'

'
Dim nextrow As Range
Set nextrow = Worksheets("Sheet B").Range("A90")
Rows("83:83").Select
Selection.Copy
Sheets("Sheet A").Select
Rows("nextrow:nextrow").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
nextrow.Value = nextrow.Value + 1
End Sub

On 02/05/2011 00:19 Gord Dibben wrote:

More typos......bad day today.

Set rng1 = Worksheets("Sheet B").Cells(Rows.Count, 1).End(xlUp) _
.Range("A83:AS83")

Change to.......

Set rng1 = Worksheets("Sheet B").Range("A83:AS83")


Gord

On Sun, 01 May 2011 19:51:09 +0100, F<news@nowhere wrote:

Hi

Thanks for that, but I'm afraid it's not pasting anything. There are no
error messages, just no discernible action.

I had been trying to get something working by recording a macro that did
part of the job: copy and paste line 83, but I'm getting 'Runtime
error: type mismatch' when I try to automate where the paste should be.

Below, 'Transfer', is what I've got so far, and if I replace the line
highlighted in the error:
'Rows("nextrow:nextrow").Select' with
'Rows("501:501").Select' it works. I see the row transferred and cell
A90 increments. I can't, however, find what is causing the problem.

Sub Transfer()
'
' Transfer Macro
'

'
Dim nextrow As Range
Set nextrow = Worksheets("Sheet B").Range("A90")
Rows("83:83").Select
Selection.Copy
Sheets("Sheet A").Select
Rows("nextrow:nextrow").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
nextrow.Value = nextrow.Value + 1
End Sub

Thanks.

Just out of interest, can anyone explain why I was getting the error at
'Rows("nextrow:nextrow").Select' in my original attempt?

--
F

Just out of interest, can anyone explain why I was
getting the error at 'Rows("nextrow:nextrow").Select'
in my original attempt?

I am guessing that nextrow is a variable name... you cannot put variable
names inside of quote marks... anything inside of quote marks are just
characters and have no meaning to VB beyond that. The correct syntax would
have been...

Rows(nextrow & ":" & nextrow).Select

However, the Rows property, unlike the Range property, does not need the
repeated same row number to work; this would have worked also...

Rows(nextrow).Select

Rick Rothstein (MVP - Excel)

F was thinking very hard :
I have a workbook with two sheets: 'Sheet A' and 'Sheet B'. 'Sheet B' holds
checkboxes with which to enter responses and the responses are aggregated
into A83:AS83.

I need to use a button to copy A83:AS83 and paste it into the first empty row
below row 500 in 'Sheet A'. As the process would be repeated more than a few
times, the number of the row into which A83:AS83 would be pasted would need
to increment automatically.

I would be grateful for any pointers on how I might be able to do this!

TIA

Try...

Sub CopyCheckBoxData()
Dim rngSource As Range, lNextRow As Long

Set rngSource = Sheets("Sheet B").Range("A83:AS83")
With Sheets("Sheet A")
lNextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
If lNextRow < 501 Then lNextRow = 501

rngSource.Copy Sheets("Sheet A").Range("A" & CStr(lNextRow))
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On 01/05/2011 21:13 GS wrote:

F was thinking very hard :
I have a workbook with two sheets: 'Sheet A' and 'Sheet B'. 'Sheet B'
holds checkboxes with which to enter responses and the responses are
aggregated into A83:AS83.

I need to use a button to copy A83:AS83 and paste it into the first
empty row below row 500 in 'Sheet A'. As the process would be repeated
more than a few times, the number of the row into which A83:AS83 would
be pasted would need to increment automatically.

I would be grateful for any pointers on how I might be able to do this!

TIA

Try...

Sub CopyCheckBoxData()
Dim rngSource As Range, lNextRow As Long

Set rngSource = Sheets("Sheet B").Range("A83:AS83")
With Sheets("Sheet A")
lNextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
If lNextRow < 501 Then lNextRow = 501

rngSource.Copy Sheets("Sheet A").Range("A" & CStr(lNextRow))
End Sub

Thanks for the suggestion: appreciated.

This is (almost) working.

It copies a line, and the row it is copied into is incremented each
time, but the line seems to be partly row 1 from 'Sheet A' which is
gradually shifted left.

The part of row 83 that it copies is also copied with #REF! in most of
the cells rather than the value in the cells.

My attempt from earlier and quoted below, copies correctly but does not
increment because of an error in the 'Rows("nextrow:nextrow").Select'
line. If I could correct that error then I might have a solution.

Sub Transfer()
'
' Transfer Macro
'

'
Dim nextrow As Range
Set nextrow = Worksheets("Sheet B").Range("A90")
Rows("83:83").Select
Selection.Copy
Sheets("Sheet A").Select
Rows("nextrow:nextrow").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
nextrow.Value = nextrow.Value + 1
End Sub

--
F

F submitted this idea :
Thanks for the suggestion: appreciated.

This is (almost) working.

It copies a line, and the row it is copied into is incremented each time, but
the line seems to be partly row 1 from 'Sheet A' which is gradually shifted
left.

The part of row 83 that it copies is also copied with #REF! in most of the
cells rather than the value in the cells.

Replace the last line in my previous sample with this revised version
that copies values only...

Sheets("Sheet A").Range("A" _
& CStr(lNextRow)).Resize(, rngSource.Columns.Count) _
= rngSource.Value

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On 01/05/2011 22:58 GS wrote:

F submitted this idea :
Thanks for the suggestion: appreciated.

This is (almost) working.

It copies a line, and the row it is copied into is incremented each
time, but the line seems to be partly row 1 from 'Sheet A' which is
gradually shifted left.

The part of row 83 that it copies is also copied with #REF! in most of
the cells rather than the value in the cells.

Replace the last line in my previous sample with this revised version
that copies values only...

Sheets("Sheet A").Range("A" _
& CStr(lNextRow)).Resize(, rngSource.Columns.Count) _
= rngSource.Value

Works a treat: many thanks!

--
F

Works a treat: many thanks!

You're welcome! Always glad to help...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On 01/05/2011 23:10 F wrote:

Works a treat: many thanks!

Looks like I spoke too soon!

I have altered one of the cells in Sheet A from
=("Discount shops: "&(COUNTIF(CI$9:CI$499,"*discount*"))) to
=("Livestock Market: "&(COUNTIF(CI$9:CI$499,"*discount*")))and the macro
has stopped working. No message, just no transfer.

Odd, as that is the only change.

--
F