A macro to take a cell and add "1"

[rdeters]

Hello everyone, this should be an easy one for me, but yet I am having some trouble. Here is the following macro I have:

Sub Copying()
' Copying Macro
' Macro recorded 5/12/2002 by DETER
Sheets("Working Database").Visible = xlSheetVisible
Sheets("Working Database").Range("A1:H5250").Copy
Sheets("IO Database").Range("A3").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Working Database").Visible = xlSheetHidden
End Sub

I would like this same macro to take the value in cell B1 on worksheet "IODatabase" and add the numerical value of "1".
Please help

[Jay Petrulis]

On 2002-05-14 09:56, rdeters wrote:
Hello everyone, this should be an easy one for me, but yet I am having some trouble. Here is the following macro I have:

Sub Copying()
' Copying Macro
' Macro recorded 5/12/2002 by DETER
Sheets("Working Database").Visible = xlSheetVisible
Sheets("Working Database").Range("A1:H5250").Copy
Sheets("IO Database").Range("A3").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Working Database").Visible = xlSheetHidden
End Sub

I would like this same macro to take the value in cell B1 on worksheet "IODatabase" and add the numerical value of "1".
Please help

Hi,

Add the following to your code and it should do what you want.

With Sheets("IODatabase")
.Range("B1") = .Range("B1") + 1
End With

Please note that you do not need to hide/unhide the sheet to work with the data

Sub Copying()

Sheets("Working Database").Range("A1:H5250").Copy
With Sheets("IO Database")
.Range("A3").PasteSpecial (xlValues)
.Range("B1") = .Range("B1") + 1
End With

End Sub

should accomplish what you want.

HTH,
Jay

[rdeters]

Jay, I tried what you indicated and it had no luck. What this count is doing, is that every time someone executes the macro, cell B1 in the worksheet "IO Database" will be incremented by 1. The reason why the worksheet "Working Database" is hidden, is because the user is not to see this sheet. Any other suggestions?

[Jay Petrulis]

Hi,

Sorry about that. I had it working at first, but changed it around and it definitely failed on my test, too.

Try this correction:

Sub Copying()

Sheets("Working Database").Visible = True
Sheets("Working Database").Range("A1:H5250").Copy
    With Sheets("IO Database")
        .Range("A3").PasteSpecial (xlValues)
        .Range("B1") = .Range("B1") + 1
    End With
Sheets("Working Database").Visible = False

End Sub

Let me know if this will work for you.

Also, is your range really that big and is it constant?

Bye,
Jay

[rdeters]

Jay, it wi=orked, thanks.
The program I am working with can in fact be taht large. The user has numerous drop down menu's that he/she has to choose from. Each drop down menu is dependant on the item the user hade chosen from the dropdown menu previously. The initial worksheet is comprised of 750 rows. The information that is chosen, is then concatenated, put throught calculations, if statements, etc. Eventually giving 7 values. These 7 values are then displayed vertically one abouve the other, in turn 7 * 750 results in 5250 rows. Quite a handy little program, that's for sure. Jay, thanks again.

[rdeters]

Jay, it wi=orked, thanks.
The program I am working with can in fact be taht large. The user has numerous drop down menu's that he/she has to choose from. Each drop down menu is dependant on the item the user hade chosen from the dropdown menu previously. The initial worksheet is comprised of 750 rows. The information that is chosen, is then concatenated, put throught calculations, if statements, etc. Eventually giving 7 values. These 7 values are then displayed vertically one abouve the other, in turn 7 * 750 results in 5250 rows. Quite a handy little program, that's for sure. Jay, thanks again.