Duplication Declaration in scope error code

J

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
443
Office Version
  1. 2019
Platform
  1. Windows
Code: 
Option Explicit
Sub Open_Workbook()
    Dim srcWB As Workbook
    Dim destWB As Workbook
    Dim fName As String
    Dim lastRow As Long
    
'   Capture current workbook as source workbook
    Set srcWB = ActiveWorkbook
'   Open destination workbook and capture it as destination workbook
    Workbooks.Open "C:\Users\jdavis\Dropbox\Quality Control\Aggregates\Recycled Concrete\Ag Base Yearly Chart.xlsx"
    Set destWB = ActiveWorkbook
    
'   Find last row of Sieve data in destination workbook
    lastRow = destWB.Sheets("Sieves").Cells(Rows.Count, "A").End(xlUp).Row + 1
    
'   Copy Sieve data from source workbook to destination workbook
    srcWB.Sheets("Ag Base").Range("H3").Copy
    destWB.Sheets("Sieves").Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("I4").Copy
    destWB.Sheets("Sieves").Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("F5").Copy
    destWB.Sheets("Sieves").Range("C" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("B4").Copy
    destWB.Sheets("Sieves").Range("D" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("F12:F18").Copy
    destWB.Sheets("Sieves").Range("G" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("D19:J19").Copy
    destWB.Sheets("Sieves").Range("H" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("A21:F21").Copy
    destWB.Sheets("Sieves").Range("I" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("F22").Copy
    destWB.Sheets("Sieves").Range("I" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("H3").Copy
    destWB.Sheets("Sieves").Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("I4").Copy
    destWB.Sheets("Sieves").Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("F5").Copy
    destWB.Sheets("Sieves").Range("C" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("B4").Copy
    destWB.Sheets("Sieves").Range("D" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("H3").Copy
    destWB.Sheets("Sieves").Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("I4").Copy
    destWB.Sheets("Sieves").Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("F5").Copy
    destWB.Sheets("Sieves").Range("C" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("B4").Copy
    destWB.Sheets("Sieves").Range("D" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("H3").Copy
    destWB.Sheets("Sieves").Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("I4").Copy
    destWB.Sheets("Sieves").Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("F5").Copy
    destWB.Sheets("Sieves").Range("C" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("B4").Copy
    destWB.Sheets("Sieves").Range("D" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("H3").Copy
    destWB.Sheets("Sieves").Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("I4").Copy
    destWB.Sheets("Sieves").Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("F5").Copy
    destWB.Sheets("Sieves").Range("C" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("B4").Copy
    destWB.Sheets("Sieves").Range("D" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("H3").Copy
    destWB.Sheets("Sieves").Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("I4").Copy
    destWB.Sheets("Sieves").Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("F5").Copy
    destWB.Sheets("Sieves").Range("C" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("B4").Copy
    destWB.Sheets("Sieves").Range("D" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("H3").Copy
    destWB.Sheets("Sieves").Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("I4").Copy
    destWB.Sheets("Sieves").Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("F5").Copy
    destWB.Sheets("Sieves").Range("C" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("B4").Copy
    destWB.Sheets("Sieves").Range("D" & lastRow).PasteSpecial xlPasteValues

'   Find last row of Durability data in destination workbook
    lastRow = destWB.Sheets("Durability").Cells(Rows.Count, "A").End(xlUp).Row + 1
    
'   Copy AC data from source workbook to destination workbook
    srcWB.Sheets("Ag Base").Range("H3").Copy
    destWB.Sheets("Durability").Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("I4").Copy
    destWB.Sheets("Durability").Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("F5").Copy
    destWB.Sheets("Durability").Range("C" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("B4").Copy
    destWB.Sheets("Durability").Range("D" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("H10").Copy
    destWB.Sheets("Durability").Range("F" & lastRow).PasteSpecial xlPasteValues
'   Find last row of Samples data in desitnation workbook
    lastRow = destWB.Sheets("Samples").Cells(Rows.Count, "A").End(xlUp).Row + 1
    
'   Copy Samples data from source workbook to destination workbook
srcWB.Sheets("Ag Base").Range("H3").Copy
    destWB.Sheets("Samples").Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("I4").Copy
    destWB.Sheets("Samples").Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("F5").Copy
    destWB.Sheets("Samples").Range("C" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("B4").Copy
    destWB.Sheets("Samples").Range("D" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("K19").Copy
    destWB.Sheets("Samples").Range("F" & lastRow).PasteSpecial xlPasteValues
        srcWB.Sheets("Ag Base").Range("K20").Copy
    destWB.Sheets("Samples").Range("G" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("Ag Base").Range("K22").Copy
    destWB.Sheets("Samples").Range("H" & lastRow).PasteSpecial xlPasteValues
'   Save changes and close destination workbook
    destWB.Close SaveChanges:=True
'   Export source workbook to PDF
    With srcWB
  Dim fName As String, LocationName As String
With activeWorksheet
     fName = Range("'Ag base'!C22").Value
     LocationName = Range("'Ag base'!B4").Value
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
             "C:\Users\jdavis\Dropbox\Quality Control\Aggregates\Recycled Concrete\" & LocationName & "\" & fName, Quality:=xlQualityStandard, _
             includeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With
End Sub

I get a duplication declaration in scope Error Code.

Code: 
'   Export source workbook to PDF
    With srcWB
  Dim fName As String, LocationName As String
With activeWorksheet
     fName = Range("'Ag base'!C22").Value
     LocationName = Range("'Ag base'!B4").Value
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
             "C:\Users\jdavis\Dropbox\Quality Control\Aggregates\Recycled Concrete\" & LocationName & "\" & fName, Quality:=xlQualityStandard, _
             includeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With
End Sub

It highlights the Fname As String
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
You already have at the top of your code this line:
Code: 
Dim fName As String
so you are declaring fName twice. Just remove one of the declarations.
 
Last edited:
Upvote 0
if I remove the "DimfName As String" from the save code it says Variable is not defined
 
Upvote 0
Code: 
'   Export source workbook to PDF
    With srcWB
     fName = Range("'Ag base'!C22").Value
     LocationName = Range("'Ag base'!B4").Value
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
             "C:\Users\jdavis\Dropbox\Quality Control\Aggregates\Recycled Concrete\" & LocationName & "\" & fName, Quality:=xlQualityStandard, _
             includeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With
End Sub

This is what I've tried now and it highlights location name and says variable is not define
 
Upvote 0
You should have only removed the fName declaration. So instead of this:

Code: 
Dim fName As String, LocationName As String

you should have this:

Code: 
Dim LocationName As String
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA Keeps Skipping a Paste
2
Replies
11
Views
715
jondavis1987
J
R
  • Question
Loop through Range and Force Formula Calculation
Replies
0
Views
161
reberryjr
R
S
  • Question
For Loop with If Statement Assistance
Replies
0
Views
227
smick
S
P
  • Question
Edit VBA code to add new attachment on Email
Replies
3
Views
271
Persl
P
G
  • Question
Sub CopyFilterPaste() error
Replies
1
Views
291
Micron
M

Forum statistics

Threads
1,214,561
Messages
6,120,239
Members
448,951
Latest member
jennlynn

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