Button to copy row to first empty row on another sheet

[F]

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

 

[Gord Dibben]

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

 

[F]

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

 

[GS]

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

 

[F]

F was thinking very hard :

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

 

[GS]

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

 

[F]

F submitted this idea :

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!

 

[GS]

Works a treat: many thanks!

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

 

[Gord Dibben]

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

 

[F]

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


Change to.......

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


Gord

Thanks.

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

 

[F]

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.

 

[GS]

F explained :

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.

Try stepping through the macro using 'F8' with the VBE window
normalized so you can watch what's going on. You may have to normalize
both wkb windows and tile them horizontal so you can keep source sheet
the active sheet but see the target sheet as you step through the code.

 

[F]

F explained :

Try stepping through the macro using 'F8' with the VBE window normalized
so you can watch what's going on. You may have to normalize both wkb
windows and tile them horizontal so you can keep source sheet the active
sheet but see the target sheet as you step through the code.

Thanks. I have done that and, simply by doing that, the sheet is working
again. Something odd is going on!

After stepping through and finding the sheet was working again, I found
that the checkboxes I have in Sheet B were inaccessible with a
'no-entry' symbol appearing when the pointer was over them. Cells,
however, were unaffected. In addition, right clicking on the button I
use to activate the macro was ineffective - no menu.

Clearly I've done something, but I don't know what!

 

[GS]

F brought next idea :

Thanks. I have done that and, simply by doing that, the sheet is working
again. Something odd is going on!

After stepping through and finding the sheet was working again, I found that
the checkboxes I have in Sheet B were inaccessible with a 'no-entry' symbol
appearing when the pointer was over them. Cells, however, were unaffected. In
addition, right clicking on the button I use to activate the macro was
ineffective - no menu.

Clearly I've done something, but I don't know what!

Sounds to me like the code 'hung' during execution. It might have
something to do with the recalcs going on when you changed the formula
just prior to copying the data. It might be a good idea to make such
changes in another procedure, then call the copy procedure. Normally,
automatic calculation would be turned off but in this case you need the
result of the revised formula to be copied. Maybe using a pause in the
code is a feasible solution. This utilizes the Sleep() function...

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

...to pause code for a specified period. (2000ms perhaps would do it)

 

[Rick Rothstein]

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]

Sounds to me like the code 'hung' during execution. It might have
something to do with the recalcs going on when you changed the formula
just prior to copying the data. It might be a good idea to make such
changes in another procedure, then call the copy procedure. Normally,
automatic calculation would be turned off but in this case you need the
result of the revised formula to be copied. Maybe using a pause in the
code is a feasible solution. This utilizes the Sleep() function...

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

..to pause code for a specified period. (2000ms perhaps would do it)

Thanks.

 

[F]

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)

Thanks: it works and I've learnt something new!