Copy Downloaded Excel sheet to Macro File

[Prakash M]

Hello Experts,

Attached is the sample excel report which is usually been downloaded. There will be no. of downloaded excel files as per Part No.

I want vba code, to run into my master macro file as below :

1. Once I upon my master macro file, I should get a pop up window which excel file and tab is to be copied to my macro file, which is named as Summary tab
2. For e.g. from attached spreadsheet (Sheet 1), first the range B6 to J17 should get copied to my summary tab into macro file from A1 cell
3. Then range B27 to K65 should get copy from J1 cell in summary tab
4. Then range D71 to K109 should get copy from T1 cell in summary tab

FYI - Range B27 to C65 has same names in range B71 to C109.

Please note as my download file is from internal application so it shows information only through column A to L.

1st table range is from Range B27:K27, if it has a second table, the range starts from B71:K71. There can also be table starting from range B115:K115 (which we currently do not have in attached)

In sheet 2 I've updated how the output should be.

Later I should get an option to save my macro file using range B1 and I1 from sheet 2 (should be named as 1243438 - Jan 16, 2020)

 

[Prakash M]

Hi,

Below is the code that I've added to copy the data from Excel.

As I've defined range 4-5 times, so every time I run the macro, I need to select the same excel report 4-5 times which I want to copy to the macro file (a pop up window opens to select the excel file)

Could you please advise to edit the macros, so that when i first run the macro, only once I should get a pop up window, on which excel file should be selected for copying the data.

Sub RectangleRoundedCorners13_Click()
Dim uploadfile As Variant
Dim uploader As Workbook
Dim CurrentBook As Workbook

Set CurrentBook = ActiveWorkbook
MsgBox ("Please select uploader file to be reviewed")
uploadfile = Application.GetOpenFilename()
    If uploadfile = "False" Then
        Exit Sub
    End If
Workbooks.Open uploadfile
Set uploader = ActiveWorkbook
With uploader
    Application.CutCopyMode = False
   
    Sheets("Report").Range("B6:J17").Copy
   
End With
CurrentBook.Activate
Sheets("Report").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
   
    Worksheets("Report").Columns("A:AG").AutoFit
       
    Worksheets("Report").Range("C:C,D:D,E:E").Delete

End Sub

Sub Table1()
Dim uploadfile As Variant
Dim uploader As Workbook
Dim CurrentBook As Workbook

Set CurrentBook = ActiveWorkbook
MsgBox ("Please select uploader file to be reviewed")
uploadfile = Application.GetOpenFilename()
    If uploadfile = "False" Then
        Exit Sub
    End If
Workbooks.Open uploadfile
Set uploader = ActiveWorkbook
With uploader
    Application.CutCopyMode = False
    Sheets("Report").Range("B27:K65").Copy
End With
CurrentBook.Activate
Sheets("Report").Range("H1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
   
    Worksheets("Report").Columns("A:AG").AutoFit
   
End Sub

Sub Table2()
Dim uploadfile As Variant
Dim uploader As Workbook
Dim CurrentBook As Workbook

Set CurrentBook = ActiveWorkbook
MsgBox ("Please select uploader file to be reviewed")
uploadfile = Application.GetOpenFilename()
    If uploadfile = "False" Then
        Exit Sub
    End If
Workbooks.Open uploadfile
Set uploader = ActiveWorkbook
With uploader
    Application.CutCopyMode = False
    Sheets("Report").Range("D71:K109").Copy
End With
CurrentBook.Activate
Sheets("Report").Range("R1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
   
    Worksheets("Report").Columns("A:AG").AutoFit
   
End Sub

Sub Table3()
Dim uploadfile As Variant
Dim uploader As Workbook
Dim CurrentBook As Workbook

Set CurrentBook = ActiveWorkbook
MsgBox ("Please select uploader file to be reviewed")
uploadfile = Application.GetOpenFilename()
    If uploadfile = "False" Then
        Exit Sub
    End If
Workbooks.Open uploadfile
Set uploader = ActiveWorkbook
With uploader
    Application.CutCopyMode = False
    Sheets("Report").Range("D115:K152").Copy
End With
CurrentBook.Activate
Sheets("Report").Range("Z1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
   
    Worksheets("Report").Columns("A:AG").AutoFit
End Sub

Sub PartPicture()
Dim uploadfile As Variant
Dim uploader As Workbook
Dim CurrentBook As Workbook

Set CurrentBook = ActiveWorkbook
MsgBox ("Please select uploader file to be reviewed")
uploadfile = Application.GetOpenFilename()
    If uploadfile = "False" Then
        Exit Sub
    End If
Workbooks.Open uploadfile
Set uploader = ActiveWorkbook
With uploader
    Application.CutCopyMode = False
   
    Worksheets("Thumbnails").Shapes("Picture 1").Copy

End With
CurrentBook.Activate
Worksheets("Report").Paste Range("A25")

Thanks !