VBA error

N

Nurzhan

Board Regular
Joined
Dec 13, 2017
Messages
60
Hi All. Pls help me. What's wrong in the following code? The line in red in returns error. But why? Both files i keep open during running macro.
------------------------------------
Sub GL_DPR_FillIn()


' Fills in Report with up-to-date data.


Dim wbA As Workbook
Dim wbB As Workbook
Dim wsName, lastRow
Dim j


Set wbA = Workbooks("GL Rates Calculation.xlsm") 'This one should already be open
Set wbB = Workbooks("DPR_ALS.xlsx")


For j = 5 To 18 Step 1

wbA.Sheets("GL").Range("AG" & "j" & ":" & "AN" & "j").Copy






If j = 5 Then wsName = "Ch22"
If j = 6 Then wsName = "Ch24"
If j = 7 Then wsName = "Ch30"
If j = 8 Then wsName = "Ch54"
If j = 9 Then wsName = "Ch56"
If j = 10 Then wsName = "Ch60"
If j = 11 Then wsName = "Ch62"
If j = 12 Then wsName = "Ch65"
If j = 13 Then wsName = "Ch67"
If j = 14 Then wsName = "Ch115b"
If j = 15 Then wsName = "Ch117"
If j = 16 Then wsName = "Ch123"
If j = 17 Then wsName = "Ch51"
If j = 18 Then wsName = "Ch124"




lastRow = Sheets(wsName).Cells(Rows.Count, "C").End(xlUp).Row
Sheets(wsName).Range("C" & lastRow + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Application.CutCopyMode = False


Next j


End Sub
 
Sort by date Sort by votes

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Re: VBA error help

Dear mrhstn!
I have another file with the same procedure but more sheets. I would like to make based on your approch but this time to copy not only values but to "paste all" with cell and value formats. How can I change it?
 
Upvote 0
Re: VBA error help

I made it with method I proposed initially. But the disadvantage is that it takes longer and you can see how this task is being implemented i.e. blinking display. I liked your approach as it's faster and almost unnoticable but it doesn't work with this because compared to my previous task, where the original table was, say, between 1 - 10 however in my current table it's spread in groups with empty rows between them, which should not be copied to the destination. For example:
A1
A2

A4
A5
 
Upvote 0
Re: VBA error help

My current code:

Sub DPR_HC_FillIn()
' Fills in Report with up-to-date data.




Dim wbA As Workbook
Dim wbB As Workbook
Dim wsName, lastRow
Dim j


Set wbA = Workbooks("Daily Production Report - December 2017.xlsm") 'This one should already be open
Set wbB = Workbooks("Single Well Daily Profile - December 2017.xlsx") 'This one should already be open


For j = 9 To 105 Step 1
ActiveWorkbook.ActiveSheet.Range("C" & j & ":" & "AD" & j).Copy


If j = 9 Then wsName = "10"
If j = 10 Then wsName = "22"
If j = 11 Then wsName = "24"
If j = 12 Then wsName = "27"
If j = 13 Then wsName = "30"
If j = 14 Then wsName = "33"
If j = 15 Then wsName = "52"
If j = 16 Then wsName = "54"
If j = 17 Then wsName = "56"
If j = 18 Then wsName = "59"
If j = 19 Then wsName = "60"
If j = 20 Then wsName = "62"
If j = 21 Then wsName = "65"
If j = 22 Then wsName = "67"
If j = 23 Then wsName = "70"
If j = 24 Then wsName = "111"
If j = 25 Then wsName = "115b"
If j = 26 Then wsName = "117"
If j = 27 Then wsName = "124"
If j = 28 Then wsName = "208"

If j = 36 Then wsName = "31"
If j = 37 Then wsName = "40"
If j = 38 Then wsName = "45"
If j = 39 Then wsName = "51"
If j = 40 Then wsName = "123"
If j = 41 Then wsName = "701"
If j = 42 Then wsName = "703"
If j = 43 Then wsName = "724"
If j = 44 Then wsName = "725"
If j = 45 Then wsName = "410"

If j = 53 Then wsName = "20"
If j = 54 Then wsName = "119"
If j = 55 Then wsName = "204"
If j = 56 Then wsName = "205"
If j = 57 Then wsName = "209"
If j = 58 Then wsName = "210"
If j = 59 Then wsName = "215"
If j = 60 Then wsName = "216"
If j = 61 Then wsName = "217"
If j = 62 Then wsName = "218"
If j = 63 Then wsName = "219"
If j = 64 Then wsName = "220"
If j = 65 Then wsName = "222"
If j = 66 Then wsName = "223"
If j = 67 Then wsName = "225"
If j = 68 Then wsName = "230"
If j = 69 Then wsName = "234"

If j = 77 Then wsName = "28"
If j = 78 Then wsName = "32"
If j = 79 Then wsName = "46"
If j = 80 Then wsName = "115"
If j = 81 Then wsName = "213"
If j = 82 Then wsName = "301"
If j = 83 Then wsName = "61"

If j = 91 Then wsName = "57"
If j = 92 Then wsName = "300"
If j = 93 Then wsName = "303"

If j = 101 Then wsName = "23"
If j = 102 Then wsName = "401"
If j = 103 Then wsName = "402"
If j = 104 Then wsName = "404"
If j = 105 Then wsName = "406"




lastRow = wbB.Sheets(wsName).Cells(Rows.Count, "D").End(xlUp).Row


wbB.Sheets(wsName).Range("C" & lastRow + 1).PasteSpecial Paste:=xlPasteAll


Next j


End Sub
 
Upvote 0
Re: VBA error help

Nurzhan said:
and you can see how this task is being implemented i.e. blinking display.
Click to expand...

Rich (BB code): 
Sub DPR_HC_FillIn()
    ' Fills in Report with up-to-date data.

    Dim wbA As Workbook, wbB As Workbook
    Dim wsName As String, j As Long

    Application.ScreenUpdating = False

    Set wbA = Workbooks("Daily Production Report - December 2017.xlsm")    'This one should already be open
    Set wbB = Workbooks("Single Well Daily Profile - December 2017.xlsx")    'This one should already be open


    For j = 9 To 105 Step 1

        If j = 9 Then wsName = "10"
        If j = 10 Then wsName = "22"
        If j = 11 Then wsName = "24"
        If j = 12 Then wsName = "27"
        If j = 13 Then wsName = "30"
        If j = 14 Then wsName = "33"
        If j = 15 Then wsName = "52"
        If j = 16 Then wsName = "54"
        If j = 17 Then wsName = "56"
        If j = 18 Then wsName = "59"
        If j = 19 Then wsName = "60"
        If j = 20 Then wsName = "62"
        If j = 21 Then wsName = "65"
        If j = 22 Then wsName = "67"
        If j = 23 Then wsName = "70"
        If j = 24 Then wsName = "111"
        If j = 25 Then wsName = "115b"
        If j = 26 Then wsName = "117"
        If j = 27 Then wsName = "124"
        If j = 28 Then wsName = "208"

        If j = 36 Then wsName = "31"
        If j = 37 Then wsName = "40"
        If j = 38 Then wsName = "45"
        If j = 39 Then wsName = "51"
        If j = 40 Then wsName = "123"
        If j = 41 Then wsName = "701"
        If j = 42 Then wsName = "703"
        If j = 43 Then wsName = "724"
        If j = 44 Then wsName = "725"
        If j = 45 Then wsName = "410"

        If j = 53 Then wsName = "20"
        If j = 54 Then wsName = "119"
        If j = 55 Then wsName = "204"
        If j = 56 Then wsName = "205"
        If j = 57 Then wsName = "209"
        If j = 58 Then wsName = "210"
        If j = 59 Then wsName = "215"
        If j = 60 Then wsName = "216"
        If j = 61 Then wsName = "217"
        If j = 62 Then wsName = "218"
        If j = 63 Then wsName = "219"
        If j = 64 Then wsName = "220"
        If j = 65 Then wsName = "222"
        If j = 66 Then wsName = "223"
        If j = 67 Then wsName = "225"
        If j = 68 Then wsName = "230"
        If j = 69 Then wsName = "234"

        If j = 77 Then wsName = "28"
        If j = 78 Then wsName = "32"
        If j = 79 Then wsName = "46"
        If j = 80 Then wsName = "115"
        If j = 81 Then wsName = "213"
        If j = 82 Then wsName = "301"
        If j = 83 Then wsName = "61"

        If j = 91 Then wsName = "57"
        If j = 92 Then wsName = "300"
        If j = 93 Then wsName = "303"

        If j = 101 Then wsName = "23"
        If j = 102 Then wsName = "401"
        If j = 103 Then wsName = "402"
        If j = 104 Then wsName = "404"
        If j = 105 Then wsName = "406"

        ActiveWorkbook.ActiveSheet.Range("C" & j & ":" & "AD" & j).Copy _
                wbB.Sheets(wsName).Cells(Rows.Count, "D").End(xlUp).Offset(1, -1)

    Next j

    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Re: VBA error help

Guys, now different error occured:
Every raw is copied correctly except those ones at the end of each table group. For example,
this line <If j = 28 Then wsName = "208"> copies rows 28, 33, 34, 35
this line <If j = 45 Then wsName = "410"> copies rows 45, 50, 51, 52
this line <If j = 69 Then wsName = "234"> copies rows 74, 75, 76 . This line totally skips required row.this line <If j = 83 Then wsName = "61"> copies rows 83, 88, 89, 90
this line <If j = 93 Then wsName = "303"> copies rows 93, 98, 99, 100

Thanks in advance!
 
Upvote 0
Re: VBA error help

This is likely because the wsName variable doesn't get reset, it retains the previous setting.

Try

Rich (BB code): 
For j = 9 To 105 Step 1
wsName = ""

You will also likely need to check that the wsName is set later on.

Rich (BB code): 
if not wsName = "" Then
ActiveWorkbook.ActiveSheet.Range("C" & j & ":" & "AD" & j).Copy _
                wbB.Sheets(wsName).Cells(Rows.Count, "D").End(xlUp).Offset(1, -1)
End if
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
WB to WB Copy-Paste Error
Replies
5
Views
508
Peter_SSs
Peter_SSs
S
  • Question Question
VBA or FORMULA for have the list of words in a range
2
Replies
13
Views
1K
samuele2723
S

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top