Duplication Declaration in scope error code

J

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
425
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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
RoryA

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
40,681
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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
J

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
425
Office Version
  1. 2019
Platform
  1. Windows
if I remove the "DimfName As String" from the save code it says Variable is not defined
 
Upvote 0
J

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
425
Office Version
  1. 2019
Platform
  1. Windows
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
RoryA

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
40,681
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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
  • Question
VBA to create a worksheet named by cell vaue if error
Replies
9
Views
628
jondavis1987
J
J
  • Locked
  • Question
VBA Error 1004 on second time running the code
Replies
1
Views
975
RoryA
RoryA
J
  • Solved
Vba Range.Value = Range.Value not working
2
Replies
10
Views
2K
Fluff
Fluff
J
  • Solved
Random Number changes while running a macro when I don't want it to.
Replies
5
Views
499
Joe4
Joe4
J
  • Question
Error in VBA I have been running for a year
2
Replies
11
Views
551
jondavis1987
J

Forum statistics

Threads
1,195,651
Messages
6,010,929
Members
441,573
Latest member
Goronvir

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
Top