Need help with 2 macros

[Buns1976]

Hi Everyone,

I need some help on the 2 Macros below. I want to run them from separate buttons in a worksheet named "Dashboard"
on a different sheet named "Day End Report" in the same workbook. The first imports data from another workbook and the
second compacts that data. At this point I can only get them to run on whichever sheet I have open at the time.

Thank You!

Private Sub CommandButton1_Click()' Macro1 Macro()
    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
    Dim rngSourceRange As Range
    Dim rngDestination As Range
    Set wkbCrntWorkBook = ActiveWorkbook
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa; *.csv"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            Workbooks.Open .SelectedItems(1)
            Set wkbSourceBook = ActiveWorkbook
            Set rngSourceRange = Application.InputBox(prompt:="Select source range", Title:="Source Range", Default:="A1:I400", Type:=8)
            wkbCrntWorkBook.Activate
            Set rngDestination = Application.InputBox(prompt:="Select destination cell", Title:="Select Destination", Default:="A1", Type:=8)
            rngSourceRange.Copy rngDestination
            wkbSourceBook.Close False
        End If
    End With
End Sub

Private Sub CommandButton3_Click()'
' Macro3 Macro
'


'
    Range("A78:I400").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlToLeft
    Columns("A:I").Select
    Range("I1").Activate
    Selection.EntireColumn.Hidden = True
    Range("K1").Select
End Sub

 

[DanteAmor]

Try this

Private Sub CommandButton1_Click() ' Macro1 Macro()
    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
    Dim rngSourceRange As Range
    Dim rngDestination As Range
    Set wkbCrntWorkBook = ActiveWorkbook
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa; *.csv"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            Workbooks.Open .SelectedItems(1)
            Set wkbSourceBook = ActiveWorkbook
            Set rngSourceRange = Application.InputBox(prompt:="Select source range", Title:="Source Range", Default:="[B][COLOR=#0000ff]'Day End Report'![/COLOR][/B]A1:I400", Type:=8)
            wkbCrntWorkBook.Activate
            Set rngDestination = Application.InputBox(prompt:="Select destination cell", Title:="Select Destination", Default:="[B][COLOR=#0000ff]'Dashboard'![/COLOR][/B]A1", Type:=8)
            rngSourceRange.Copy rngDestination
            wkbSourceBook.Close False
        End If
    End With
End Sub

Private Sub CommandButton3_Click() '
' Macro3 Macro
[B][COLOR=#0000ff]    Set sh = Sheets("Dashboard")[/COLOR][/B]
    [COLOR=#0000ff]sh[/COLOR].Range("A78:I400").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
    [COLOR=#0000ff]sh[/COLOR].Columns("A:I").EntireColumn.Hidden = True
End Sub

 

[Buns1976]

Hi Dante,

I probably didn't explain this properly. The SOURCE file is fine the way it was. We pick that daily from a directory.
The "destination" would be Cell A1 in the Day End Report sheet. I had tried the same solution as yours and it still
imports to the Dashboard sheet.

Sub Import_Click()

    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
    Dim rngSourceRange As Range
    Dim rngDestination As Range
    Set wkbCrntWorkBook = ActiveWorkbook
    With Application.FileDialog(msoFileDialogOpen)
        .Filters.Clear
        .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa; *.csv"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            Workbooks.Open .SelectedItems(1)
            Set wkbSourceBook = ActiveWorkbook
            Set rngSourceRange = Application.InputBox(prompt:="Select source range", Title:="Source Range", Default:="A1:I400", Type:=8)
            wkbCrntWorkBook.Activate
            Set rngDestination = Application.InputBox(prompt:="Select destination cell", Title:="Select Destination", Default:='Day End Report'!A1", Type:=8)
            rngSourceRange.Copy rngDestination
            wkbSourceBook.Close False
        End If
    End With
End Sub

 

[DanteAmor]

So I still do not understand, which macro do you want to change and which line?
And what would you like to put on the line?

 

[Buns1976]

Dante,

I'm not certain which line either. As written it should go to Cell A1 in the Day End Report. I think I'll add some code to the macro to unhide
the Day End Report sheet at the beginning of the macro and and then hide it at the end. Kind of the long way around the barn unless there
is another idea out there?

Thanks a lot!

 

[DanteAmor]

You can explain again what you want to copy and where you want to paste it.
I assumed with my example you could adapt the macro to paste on the sheet that you want.
But I still do not understand which sheet you want to paste on.
If you explain me with examples, I'll gladly help you.

 

[Buns1976]

Hi Dante,

I was able to get it resolved.

Thanks for your help!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.