Copy / Paste between workbooks into a specific worksheet query?!
 

Hi all,

Can anybody please help.. i have the code below (many thanks to the
originator), but, i cannot get it to copy to an existing range in a
worksheet in my destination called 'status' i.e. something like Set
sh = Sheets("Status").Range("a2").. any help much appreciated.

Cheers

P

' SELECT THE FILES FROM THE FOLDER - HOLD CTRL



Sub GetData_Extract_Qs()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant, N As Long
Dim rnum As Long, DestRange As Range
Dim sh As Worksheet

SaveDriveDir = CurDir
MyPath = "C:\project info\Monthly Report\Final Versions"
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel
Files,*.xls", _
MultiSelect:=True)
If IsArray(FName) Then

' Sort the Array
FName = Array_Sort(FName)

Application.ScreenUpdating = False
'Add worksheet to the Activeworkbook and use the Date/Time as
name

Set sh = ActiveWorkbook.Worksheets.Add

sh.Name = Format("all") '<-- how do i change this????

'Loop through all files you select in the GetOpenFilename
dialog
For N = LBound(FName) To UBound(FName)

'Find the last row with data
rnum = LastRow(sh)

'create the destination cell address

Set DestRange = sh.Cells(rnum + 1, "A")

' For testing Copy the workbook name in Column E
sh.Cells(rnum + 1, "E").Value = FName(N)

'Get the cell values and copy it in the destrange
'Change the Sheet name and range as you like
GetData FName(N), "SECTION 6", "B14:J22", DestRange, False,
False

Next
End If

ChDrive SaveDriveDir
ChDir SaveDriveDir


Application.ScreenUpdating = True
End Sub

Copy / Paste between workbooks into a specific worksheet query?!
 

Hi JLGWhiz,

Sorry, i think i have misled you.. i dont want to change the sheet
name, i want to find an existing sheet (in the open workbook) called
'Status' and copy the data (from the closed workbook) into that tab.

Many thanks all the same.

Vbr

P

JLGWhiz wrote:
sh.Name = Format("all") '<-- how do i change this????

sh.Name = Format("status")



" wrote:

Hi all,

Can anybody please help.. i have the code below (many thanks to the
originator), but, i cannot get it to copy to an existing range in a
worksheet in my destination called 'status' i.e. something like Set
sh = Sheets("Status").Range("a2").. any help much appreciated.

Cheers

P

' SELECT THE FILES FROM THE FOLDER - HOLD CTRL



Sub GetData_Extract_Qs()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant, N As Long
Dim rnum As Long, DestRange As Range
Dim sh As Worksheet

SaveDriveDir = CurDir
MyPath = "C:\project info\Monthly Report\Final Versions"
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel
Files,*.xls", _
MultiSelect:=True)
If IsArray(FName) Then

' Sort the Array
FName = Array_Sort(FName)

Application.ScreenUpdating = False
'Add worksheet to the Activeworkbook and use the Date/Time as
name

Set sh = ActiveWorkbook.Worksheets.Add

sh.Name = Format("all") '<-- how do i change this????

'Loop through all files you select in the GetOpenFilename
dialog
For N = LBound(FName) To UBound(FName)

'Find the last row with data
rnum = LastRow(sh)

'create the destination cell address

Set DestRange = sh.Cells(rnum + 1, "A")

' For testing Copy the workbook name in Column E
sh.Cells(rnum + 1, "E").Value = FName(N)

'Get the cell values and copy it in the destrange
'Change the Sheet name and range as you like
GetData FName(N), "SECTION 6", "B14:J22", DestRange, False,
False

Next
End If

ChDrive SaveDriveDir
ChDir SaveDriveDir


Application.ScreenUpdating = True
End Sub

Copy / Paste between workbooks into a specific worksheet query
 

Hi Tom.

Once again you have performed a miracle!!!

many many many thanks

Paul
:-)

Tom Ogilvy wrote:
Set sh = ActiveWorkbook.Worksheets.Add
sh.Name = Format("all")

would become the single line:

set sh = worksheets("Status")

--
Regards,
Tom Ogilvy


" wrote:

Hi JLGWhiz,

Sorry, i think i have misled you.. i dont want to change the sheet
name, i want to find an existing sheet (in the open workbook) called
'Status' and copy the data (from the closed workbook) into that tab.

Many thanks all the same.

Vbr

P

JLGWhiz wrote:
sh.Name = Format("all") '<-- how do i change this????

sh.Name = Format("status")



" wrote:

Hi all,

Can anybody please help.. i have the code below (many thanks to the
originator), but, i cannot get it to copy to an existing range in a
worksheet in my destination called 'status' i.e. something like Set
sh = Sheets("Status").Range("a2").. any help much appreciated.

Cheers

P

' SELECT THE FILES FROM THE FOLDER - HOLD CTRL



Sub GetData_Extract_Qs()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant, N As Long
Dim rnum As Long, DestRange As Range
Dim sh As Worksheet

SaveDriveDir = CurDir
MyPath = "C:\project info\Monthly Report\Final Versions"
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel
Files,*.xls", _
MultiSelect:=True)
If IsArray(FName) Then

' Sort the Array
FName = Array_Sort(FName)

Application.ScreenUpdating = False
'Add worksheet to the Activeworkbook and use the Date/Time as
name

Set sh = ActiveWorkbook.Worksheets.Add

sh.Name = Format("all") '<-- how do i change this????

'Loop through all files you select in the GetOpenFilename
dialog
For N = LBound(FName) To UBound(FName)

'Find the last row with data
rnum = LastRow(sh)

'create the destination cell address

Set DestRange = sh.Cells(rnum + 1, "A")

' For testing Copy the workbook name in Column E
sh.Cells(rnum + 1, "E").Value = FName(N)

'Get the cell values and copy it in the destrange
'Change the Sheet name and range as you like
GetData FName(N), "SECTION 6", "B14:J22", DestRange, False,
False

Next
End If

ChDrive SaveDriveDir
ChDir SaveDriveDir


Application.ScreenUpdating = True
End Sub