Need help deciphering VBA code

[ugkwan]

Hi,

Can someone please take a look at the code below? I keep getting an error when trying to run the code. The code was given to me by a co worker whom is no longer around. I need help because I'm too amateur with VBA and need some pro tip. Thanks!

About the code: It's suppose to copy specific fields from one worksheet to another worksheet.


Sub PasteLinks()

Dim ibox1 As Integer, ibox2 As Integer
Dim addy As String
Dim WSName1 As String, WSName2 As String

WSName1 = "ALLFUNDS_BS" 'copy from sheet
WSName2 = "NonMajor Special Revenue BS" 'paste to sheet

Worksheets(WSName1).Activate
ibox1 = InputBox("Please input row # to copy from")
ibox2 = InputBox("Please input row # to paste to")

Worksheets(WSName2).Activate
Range("G" & ibox2).Activate 'change this to first pasted column

Worksheets(WSName1).Activate
Range("L" & ibox1).Activate 'change this to first copied column

Do
addy = ActiveCell.Address(False, False)

Worksheets(WSName2).Activate
ActiveCell.Value = "=IF(" & WSName1 & "!" & addy & "="""",""""," & WSName1 & "!" & addy & ")"
ActiveCell.Offset(0, 2).Activate 'change this IF no blank column in between

Worksheets(WSName1).Activate
ActiveCell.Offset(0, 2).Activate 'change this IF no budget + 2 blank columns in between
Loop Until ActiveCell.Address(False, False) = "BL" & ibox1 'change BL to last column you want copied + offset number above

Worksheets(WSName2).Activate

End Sub

 

[Caleeco]

Hello ugkwan,

Which line is the error occuring on? At a quick guess, maybe try changing the section of code below (adding the worksheet before the range):

Worksheets(WSName2).Activate
Worksheets(WSName2).Range("G" & ibox2).Activate 'change this to first pasted column


Worksheets(WSName1).Activate
Worksheets(WSName1).Range("L" & ibox1).Activate 'change this to first copied column

Let me know which line is causing the problem and i can trouble shoot
Thanks
Caleeco

 

[ugkwan]

Hi Caleeco,

I've added your suggestion and it's still giving me an error

I'm getting a "run-time error '9': script out of range"

Please see screenshot. Maybe it will better explain why I'm failing.
*I've added the row and column to use in my worksheet.

Imgur: The most awesome images on the Internet

Thanks for your help and time!

 

[Michael M]

A screenshot of the code is fairly useless in this case !!
Have you checked to confirm that these sheet names in the code are exactly the same as the actual sheetnames ?
Make sure there are no leading / trailling spaces in the sheet tab names !!

"ALLFUNDS_BS"
"NonMajor Special Revenue BS"

It would be easier if you have further problems to upload your workbook to Dropbox or similar and then post a link to it back here.

 

[Milo_Minderbinder]

Either hit "Debug" when it errors or step through the code using F8 while looking at the code. That will tell you where the error is.

Also, the picture doesn't match the code posted. What's happening with those inputboxes?

 

[ugkwan]

Hi everyone,

I've tried to debug without success. I'm too new at this stuff, but am intent on learning it. I'm trying to get the macro to run and produce some output.

I've attached a copy of my workbook in the following link:
https://www.dropbox.com/s/u54l0hdgttz0q7p/PasteLink(Mr.Excel2).xlsx?dl=0

Thanks for the constructive comments so far. It has helped me understand what should be prioritized.

 

[Michael M]

In your dropbox example, you don't have a worksheet called "ALLFUNDS_BS" ??
That's what is causing the error !!

 

[ugkwan]

Yes, the example does not have a ALLFUNDS_BS. I know it doesn't reflect the code from the original post. I've updated WSName1 and WSName2 to Payroll Tester and Payroll Tester 2 respectively. I don't understand why the macro doesn't run. It's suppose to ask for which row to start copying and pasting. I'm sorry if it is confusing because I've changed the naming in order to try and get the code to work for the workbook.

 

[Michael M]

Yep....sure is confusing !!
If you are going to post your worksheets / workbook and codes, you need to ensure it is what you are using and what we are trying to solve !!

 

[6StringJazzer]

This thread has been cross-posted to Please help decipher VBA code