Copy data from a closed workbook and paste it in a specific sheet of an open workbook

Mater

New Member
Joined
Jul 16, 2019
Messages
9
Good day all,
This is my first post and I am looking for some help with VBA. I am new to coding and macros so what I have below has been copied from other posts on this site but it does't seem to work as I had hoped.

Desired function: I have a Macro Enabled Template workbook, "MODIFICATION TEMPLATE", with a few other macros on the first worksheet named "Command Sheet". I have created a Command Button "Import FSC" and what I had hoped it to do was to create a new sheet after "Command Sheet", and name it "FSC Temp Sheet". The code would then open a dialog box to the file location of the .xls source file in which I could select the most recent file downloaded from a website, allow me to select the workbook needed, copy the data from sheet 1, paste it into the newly created "FSC Temp Sheet" and lastly close the source sheet without saving. Disclosure: I download a new copy of the source sheet each week so both the workbook and worksheet change each time which is why I chose to copy "Sheet 1".
Problem: The below code works perfectly (after much trial, frustration and reading). As I step through each line of the code, a new sheet is created and named correctly, the dialog box opens and I can select the file I want to copy, the .xls file opens momentarily, then switches back to the "MODIFICATION TEMPLATE" workbook and the "FSC Temp Sheet", and finally closes the source sheet but the data is not pasted into the "FSC Temp Sheet".

Lastly, I chose this method mainly because I don't know any better but ultimately once the data is in the FSC Temp Sheet, I can then run another macro to modify the rows and columns as I need for analysis.

I apologize for the long winded post but thought it was better to over explain. Any help or better suggestions would be greatly appreciated.
Thank you
Mater

Code:
Option ExplicitSub Paste_New_FSC_Data()
    'Opens a new worksheet for FSC data to be copied to
    Dim ws As Worksheet
    With ThisWorkbook
        Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        ws.Name = "FSC Temp Sheet"
        Worksheets("FSC Temp Sheet").Activate
    End With
    ' Opens the FSC weekly folder to select current weeks FSC Download


    Dim wbThisWB    As Workbook
    Dim wbImportWB  As Workbook
    Dim strFullPath As String
    Dim lngLastRow  As Long
    Dim lngLastCol  As Long
    
    Set wbThisWB = ThisWorkbook
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Clear
        .AllowMultiSelect = False
        .Title = "Please select most recent FSC Download:"
        .Filters.Add "Excel files", "*.xls", 1
        .InitialFileName = "H:\MY WORKING FILES\Daves Weekly FSC Files"
        .Show
        On Error Resume Next 'In case the user has clicked the button
            strFullPath = .SelectedItems(1)
            If Err.Number <> 0 Then
                wbThisWB = Nothing
                Exit Sub 'Error has occurred so quit
            End If
        On Error GoTo 0
    End With
    
    Application.ScreenUpdating = False
    
    Set wbImportWB = Workbooks.Open(strFullPath)
    'code here to copy and paste tab from Import WB into the current workbook
    On Error Resume Next 'In case there's no data or tab doesn't exist
        With wbImportWB.Sheets("Sheet 1")
            lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            If lngLastRow > 0 And lngLastCol > 0 Then
                'If the 'lngLastRow' and 'lngLastCol' variable have been set there's data to be copied.
                'The following copies the entire range from tab 'Extract' in the import workbook to cell A1 in 'Sheet1' of this workbook (change to suit).
                Range(.Cells(1, 1), .Cells(lngLastRow, lngLastCol)).Copy wbThisWB.Sheets("Sheet 1").Cells(1, 1)
            End If
        End With
    On Error GoTo 0
    
    wbImportWB.Close False 'Close the Import WB without saving any changes.
    
    Set wbThisWB = Nothing
    Set wbImportWB = Nothing
    
    Application.ScreenUpdating = True
    
End Sub
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Try putting a period at the beginning of this line of code:
Code:
 [COLOR="#FF0000"][B].[/B][/COLOR]Range(.Cells(1, 1), .Cells(lngLastRow, lngLastCol)).Copy wbThisWB.Sheets("Sheet 1").Cells(1, 1)
 
Upvote 0
Good afternoon mumps,
Thank you for the reply. I did put a period in front as Range as suggested but it didn't work. I also changed "Sheet 1" to "FSC Temp Sheet" which did not work either. I will keep trying other things.
I appreciate the help
Mater
 
Upvote 0
Perhaps you could upload a copy of your "MODIFICATION TEMPLATE" file including any macros your are currently using and at least one copy of a source file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. If the workbooks contain confidential information, you could replace it with generic data.
 
Last edited:
Upvote 0
Unfortunately I am not able to upload or download data from this computer. Do you think there may be a conflict with another Macro? None of them are currently being used until this step is complete.
Mater
 
Upvote 0
When you step through each line of the code, stop a this line:
Code:
If lngLastRow > 0 And lngLastCol > 0 Then
Hold the cursor over lngLastRow and then over lngLastCol. Is a value displayed for each variable?
 
Upvote 0
Neat trick, yes, in each instance the bubble is as follows: lngLastRow = 0 and lngLastCol = 0.

So if I understand this means that it excel hasn't found any data to copy. Is that correct? This particular sheet that I am attempting to copy has columns A-N and 512 rows. For future sheets it should always be A-N but the number of rows will vary week to week.
 
Upvote 0
If they were both equal to zero, than nothing will be copied. Try replacing
Code:
lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
with
Code:
lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
    lngLastCol = ActiveSheet.UsedRange.Columns.Count
and step through again. See if any values are returned.
 
Last edited:
Upvote 0
I just noticed that in this line
Code:
With wbImportWB.Sheets("Sheet 1")
you have a space between "Sheet" and "1". Should that space be there? Try removing that space so you have
Code:
With wbImportWB.Sheets("Sheet1")
 
Last edited:
Upvote 0
Ok, made the changes, did not paste the data but the bubbles were as follows:

(In the string lngLastRow = Range ("A" & Rows.Count).End(xlUp).Row) - lngLastRow = 0; Rows.Count = 1048576; and .Row = 1
(In the string lngLastCol = ActiveSheet.UsedRange.Columns.Count) - lngLastCol = 0
(In the string lngLastRow > 0 And lngLastCol >0 Then - lngLastRow = 1; lngLastCol = 14
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top