Carlit007
New Member
- Joined
- Sep 5, 2018
- Messages
- 47
- Office Version
- 2019
- 2016
- 2013
- Platform
- Windows
- MacOS
Hi I have the following 2 subs which I have been able to make work independently but would like to figure out how to combine into one task.
If anybody could help this Novice it would be greatly apreciated
SUB #1 (ImportData) which is the work in progress its Purpose is to give user an open file option to import data into current workbook which is a template
1.) user clicks on a button
2.) user gets a file prompt to choose the file & Open workbook to import data from another workbook generated from a system report
3.) Sub #2 Below is supposed to happen to the newly open file
4.) Data From Open file is supposed to be Pasted into template workbook WB in worksheet "Unit Roster" cell Range B2
SUB #2 (MergeShData) does the following: to a worksheet which is a workbook that is generated from a report
1.)It clears whatever data is in sheet1 and renames it to "master"
2. )it looks at all worksheets (Except "Master") in workbook and copies the data from region starting B2 to the "Master" worksheet
3.)It them sorts the data
Here is the Code For SUB# 1 Called ImportData note this one is work in progress
And this is the VBA Code for SUB#2 (MergeShData) I got it to do mostly everything I want it to do
What I want to do is basically
1. run the VBA code in SUB#1 to initiate file open and choosing the file to get data from
2. Sub#2 carries out its function listed above without saving the changes to the chosen file.
3. Data from the newly open workbook # 2, Worksheet "Master" is copied to template workbook containing a worksheet named "Unit Roster" Range (B2)
If anybody could help this Novice it would be greatly apreciated
SUB #1 (ImportData) which is the work in progress its Purpose is to give user an open file option to import data into current workbook which is a template
1.) user clicks on a button
2.) user gets a file prompt to choose the file & Open workbook to import data from another workbook generated from a system report
3.) Sub #2 Below is supposed to happen to the newly open file
4.) Data From Open file is supposed to be Pasted into template workbook WB in worksheet "Unit Roster" cell Range B2
SUB #2 (MergeShData) does the following: to a worksheet which is a workbook that is generated from a report
1.)It clears whatever data is in sheet1 and renames it to "master"
2. )it looks at all worksheets (Except "Master") in workbook and copies the data from region starting B2 to the "Master" worksheet
3.)It them sorts the data
Here is the Code For SUB# 1 Called ImportData note this one is work in progress
VBA Code:
Sub [B]ImportData[/B]() '[COLOR=rgb(97, 189, 109)] this is my attempt to copy data after SUB# was done independently [/COLOR]
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(title:="Browse for your file & import range", FileFilter:="excel files(*.xls*),*xls*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Worksheets("Master").Range("B2").Copy
'CurrentRegion.Sort key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo
ThisWorkbook.Worksheets("Sheet1").Range("B2").Paste
OpenBook.Close False
End If
Application.ScreenUpdating = True
End Sub
And this is the VBA Code for SUB#2 (MergeShData) I got it to do mostly everything I want it to do
VBA Code:
Sub [B]MergeShData[/B]() 'use this to combine and export to template
Application.ScreenUpdating = False
Dim Sht As Worksheet
'Range("A1", [B4].SpecialCells(xlLastCell)).ClearContents
Cells.Select
'Range("J14").Activate
Selection.ClearContents
Selection.Delete Shift:=xlUp
Sheets("Sheet1").Name = "Master"
'Range("A1").Select
For Each Sht In ActiveWorkbook.Worksheets
Sht.Activate
If Sht.Name <> "Master" And Sht.Range("B4").Value <> "" Then
Lastrow = Range("B65536").End(xlUp).Row
Columns("C:C").Delete Shift:=xlToLeft 'delete if not working
Range("B4", Cells(Lastrow, "M")).Copy
Sheets("Master").Select
Range("B65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
'Columns("C:C").Delete 'Shift:=xlToLeft
End If
Next Sht
Application.CutCopyMode = False
Sheets("Master").Range("B2").CurrentRegion.Sort key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo
'use this code to make sure the colums autofit
ActiveSheet.Cells.WrapText = False
ActiveSheet.Cells.EntireColumn.AutoFit
'Range("A1").Select
'Columns("B:B").Delete Shift:=xlToLeft
'Selection.Delete Shift:=xlToLeft
Application.ScreenUpdating = True
End Sub
What I want to do is basically
1. run the VBA code in SUB#1 to initiate file open and choosing the file to get data from
2. Sub#2 carries out its function listed above without saving the changes to the chosen file.
3. Data from the newly open workbook # 2, Worksheet "Master" is copied to template workbook containing a worksheet named "Unit Roster" Range (B2)