Find sheet to pull data using sheet name in another workbook.

[ChrisRamsden]

Hi again everyone,

Got another problem i would love some help with, and after having so much success here recently i wanted to challenge you all again. I am currently writing a macro to scan through multiple files looking for the sheet name which matches a cell reference in another workbook (where the macro is triggered from.) The problem i am having is i can't get it to find the sheet, as i believe the issue is the reference cell.

The reference cell is D9 which contains the value 01/01/18 but is formatted to appear as Jan. I built this into my code but i can't get it working. I think the rest of the macro will work fine once it is able to find the correct sheet.

Dim Wb2 As Workbook
Dim Wks As Worksheet, sh As Worksheet, Dsh As Worksheet
Dim Rng As Range, c As Range
Dim Rw As Range
Dim i As Long
Dim toprow As Long

'Stop background controls to improve macro efficiency
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'Set perameters
mainpath = Range("b17").Value
Set mainfile = Workbooks("PSR Suite 2018 - Test 2.xlsm")
Set Dsh = mainfile.Sheets("Disputes Log v2")
Set Wks = mainfile.Sheets("Update")
Set Rng = Wks.Range("D9")
month = Format(Rng.Value, "MMM")

'Populate Disputes Log v2
'Open file selection panel to select files
With Application.FileDialog(3)
.InitialFileName = "Filepath here"
.Show
For Each myFile In .SelectedItems
Set Wb2 = Workbooks.Open(myFile)

'From each file select data to copy & paste into spreadsheet.
For Each sh In Sheets
If sh.Name = month Then
variable = Cells(Rows.Count, 3).End(xlUp).Row
Rows("2:" & variable).Copy
mainfile.Sheets("Disputes Log v2").Activate
Range("C" & Rows.Count).End(xlUp).Offset(1).Select
Selection.Offset(0, -2).PasteSpecial PasteSpecial:=PasteValue
End If
Next sh
Wb2.Close False
Next myFile

'Add Month and Year to data
toprow = 1 'Change this to whatever the top row is
For i = toprow To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 3) > "" And Cells(i, 10) = "" Then
Wks.Range("D9").Copy
Cells(i, 10).PasteSpecial PasteSpecial:=PasteValue
Wks.Range("D10").Copy
Cells(i, 11).PasteSpecial PasteSpecial:=PasteValue
End If
Next i

'Reset background controls
Application.DisplayAlerts = True
Application.ScreenUpdating = True

'Report out the Macro has finished
MsgBox "Macro Complete"

End With
End Sub

Any help would be greatly appreciated.

Thank you,

Chris

 

[offthelip]

Have you tried converting the month number to text using code such as this:

Month = Left(MonthName(Month(Rng.Value)), 3)

instead of :

Month = Format(Rng.Value, "MMM")

 

[Fluff]

Try

   Dim Mnth As String
   Mnth = Wks.Range("D9").Text
   For Each sh In Sheets
      If sh.Name = Mnth Then

For reference it's best to avoid using VBA keywords (such as Month) for variable names

 

[ChrisRamsden]

Hi again,

offthelip - Tried your code and i got the error "Compile error - expected array" on the line you suggested.
Fluff - Tried your code and it worked at finding the correct sheet so thank you so much for this.

On the back of this i now have an error on line

Selection.Offset(0, -2).PasteSpecial PasteSpecial:=PasteValue

stating Run-time error 1004 - Application defined or object defined error

Any suggestions on how to correct this?

Thank you

 

[ChrisRamsden]

Hi Everyone,

Thanks again to Fluff for sorting my problem out, wanted to share the completed code after working out the additional problem myself. If anyone is particularly good at cleaning up code, please feel free to have a stab at this one lol

Dim Wb2 As Workbook
Dim Wks As Worksheet, sh As Worksheet, Dsh As Worksheet
Dim Rng As Range, c As Range
Dim Rw As Range
Dim i As Long
Dim toprow As Long
Dim Mnth As String

'Stop background controls to improve macro efficiency
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'Set perameters
mainpath = Range("b17").Value
Set mainfile = Workbooks("PSR Suite 2018 - Test 2.xlsm")
Set Dsh = mainfile.Sheets("Disputes Log v2")
Set Wks = mainfile.Sheets("Update")
Set Rng = Wks.Range("D9")
Mnth = Wks.Range("D9").Text

'Populate Disputes Log v2
'Open file selection panel to select files
With Application.FileDialog(3)
.InitialFileName = "T:\AM1DATA\Customer Improvement Team\Lean\Lean and CI\Projects\Fraud Telephony 2019\PSR Manual"
.Show
For Each myFile In .SelectedItems
Set Wb2 = Workbooks.Open(myFile)

'From each file select data to copy & paste into spreadsheet.
For Each sh In Sheets
If sh.Name = Mnth Then
sh.Activate
variable = Cells(Rows.Count, 3).End(xlUp).Row
Rows("2:" & variable).Copy
mainfile.Sheets("Disputes Log v2").Activate
Range("C" & Rows.Count).End(xlUp).Offset(0, -2).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial
Sheets("Disputes Log v2").Cells.FormatConditions.Delete
End If
Next sh
Wb2.Close False
Next myFile

'Add Month and Year to data
toprow = 1 'Change this to whatever the top row is
For i = toprow To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 3) > "" And Cells(i, 10) = "" Then
Wks.Range("D9").Copy
Cells(i, 10).PasteSpecial
Wks.Range("D10").Copy
Cells(i, 11).PasteSpecial
End If
Next i

'Reset background controls
Application.DisplayAlerts = True
Application.ScreenUpdating = True

'Report out the Macro has finished
MsgBox "Macro Complete"

End With
End Sub

Thank you very much in advance

Chris