Please help make this junk code work

[Melissa82]

Hi,

I am horrible at VBA unless I can copy and paste or record macros. Unfortunatly my code is a giant mess and takes forever to run/looks buggy. It works but I know it could be better.

I am basically running a button that triggers a bunch of Macros because I can't figure out how to edit my one good code to do the following

- I need the workbooks to be copied into specific master worksheets, ideally replacing any data already there (Property, Group Pickup, Segment, Pace, Redemptions, TMTP) No formatting needed, just copy paste
- Those 6 worksheets need to be hidden once complete
- One of the single sheet workbooks has a worksheet named 'Pace Demand (56)" where the number is always changing. All other worksheets have a static worksheet name that is not repeated in the other files but workbook
names that are always different. This is the one thing my junk code can't do so I am manually changing names.

The worksheet names are matched up like this if it helps:

Property > Property
Pick Up Report - Business View > Group Pickup
Pick Up Report - Business View > Segments
Pace Demand (56) > Pace
Redemption Rooms on the Books R > Redemptions
Current > TMTP


Good Import Code

VBA: How to import multiple worksheets into 1 workbook, separate tabs​

Sub CopySheets()
Application.ScreenUpdating = False
Dim fd As FileDialog, lRow As Long, vSelectedItem As Variant, srcWB As Workbook, desWB As Workbook
Set desWB = ThisWorkbook
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = True
If .Show = -1 Then
For Each vSelectedItem In .SelectedItems
Set srcWB = Workbooks.Open(vSelectedItem)
Sheets.Copy after:=desWB.Sheets(desWB.Sheets.Count)
srcWB.Close False
Next
Else
End If
End With
Application.ScreenUpdating = True
End Sub

Junk Code

Sub UpdateData()
Application.Run "'1 - Daily Detail Master- VBA Test.xlsm'!UnhideClear"
Application.Run "'1 - Daily Detail Master- VBA Test.xlsm'!CopySheets"
Application.Run "'1 - Daily Detail Master- VBA Test.xlsm'!PasteData"
Application.CutCopyMode = False
Application.Run "'1 - Daily Detail Master- VBA Test.xlsm'!DeleteHide"
Sheets("Daily Detail").Select
End Sub
Sub UnhideClear()
Sheets("GDM").Select
Sheets("Property").Visible = True
Sheets("Property").Select
Sheets("Segments").Visible = True
Sheets("Segments").Select
Sheets("Group Pickup").Visible = True
Sheets("Segments").Select
Sheets("Pace").Visible = True
Sheets("Pace").Select
Sheets("TMTP").Visible = True
Sheets("TMTP").Select
Sheets("Redemptions").Visible = True
Sheets(Array("Property", "Group Pickup", "Segments", "Pace", "Redemptions", "TMTP")) _
.Select
Sheets("Property").Activate
Cells.Select
Selection.ClearContents
Sheets("Daily Detail").Select
End Sub
Sub CopySheets()
Application.ScreenUpdating = False
Dim fd As FileDialog, lRow As Long, vSelectedItem As Variant, srcWB As Workbook, desWB As Workbook
Set desWB = ThisWorkbook
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = True
If .Show = -1 Then
For Each vSelectedItem In .SelectedItems
Set srcWB = Workbooks.Open(vSelectedItem)
Sheets.Copy after:=desWB.Sheets(desWB.Sheets.Count)
srcWB.Close False
Next
Else
End If
End With
Application.ScreenUpdating = True
End Sub
Sub PasteData()
Sheets("Property (2)").Select
Cells.Select
Selection.Copy
Sheets("Property").Select
Cells.Select
ActiveSheet.Paste
Sheets("Pick Up Report - Business Type").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Group Pickup").Select
Cells.Select
ActiveSheet.Paste
Sheets("Pick Up Report - Business View").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Segments").Select
Cells.Select
ActiveSheet.Paste
Sheets("Pace Demand (56)").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Pace").Select
Cells.Select
ActiveSheet.Paste
Sheets("Current").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("TMTP").Select
Cells.Select
ActiveSheet.Paste
Sheets("Redemption Rooms on the Books R").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Redemptions").Select
Cells.Select
ActiveSheet.Paste
End Sub
Sub DeleteHide()
Sheets(Array("Pace Demand (56)", "Summary", "Current", "Previous", _
"Redemption Rooms on the Books R", "Property (2)", _
"Pick Up Report - Business Type", "Pick Up Report - Business View")).Select
Sheets("Pick Up Report - Business Type").Activate
ActiveWindow.SelectedSheets.Delete
Sheets(Array("Property", "Group Pickup", "Segments", "Pace", "Redemptions", "TMTP")) _
.Select
Sheets("Property").Activate
ActiveWindow.SelectedSheets.Visible = False
End Sub

 

[Melissa82]

I fixed most of it on my own so this is solved.

VBA - How do I speed this code up?

I am extremelly new to VBA but relatively competant at googling and figuring it out. I have manage to put this code together for a file I run constantly but it is fairly slow. I know I am not doing things in the best way but if anyone has some ideas for speeding some of the more repetative...

www.mrexcel.com

 

[smozgur]

Good to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution or a link to the solution. The link you provided doesn't answer this question.