Help please - how to set target worksheet?
Results 1 to 5 of 5

Thread: Help please - how to set target worksheet?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Help please - how to set target worksheet?

    Hi everyone

    Please could someone try and help me?

    I am using the below code which works, it essentially is importing a set range of data from one worksheet into my main worksheet.

    What I need to do is set the worksheet name as currently it just imports the data into the activework sheet.

    Can anyone help me please? Say using the worksheet name as 'WorksheetName'.

    Code:
    Sub Import1()
    
    
    ' Get customer workbook...
    Dim customerBook As Workbook
    Dim filter As String
    Dim caption As String
    Dim customerFilename As String
    Dim customerWorkbook As Workbook
    Dim targetWorkbook As Workbook
    
    
    
    
    ' make weak assumption that active workbook is the target
    Set targetWorkbook = Application.ActiveWorkbook
    
    
    
    
    ' get the customer workbook
    filter = "Text files (*.xls),*.xls"
    caption = "Please select the Timesheets "
    customerFilename = Application.GetOpenFilename(filter, , caption)
    
    
    Set customerWorkbook = Application.Workbooks.Open(customerFilename)
    
    
    ' assume range is A2 - K200 in sheet1
    ' copy data from customer to target workbook
    Dim targetSheet As Worksheet
    Set targetSheet = targetWorkbook.Worksheets(1)
    Dim sourceSheet As Worksheet
    Set sourceSheet = customerWorkbook.Worksheets(1)
    
    
    targetSheet.Range("A2", "K500").Value = sourceSheet.Range("A2", "K500").Value
    
    
    ' Close customer workbook
    customerWorkbook.Close
    
    
    ' targetSheet.Range("M3").Value = customerFilename
    
    
    Range("N4").Select
        ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    
    
    Range("A1").Select
    End Sub

  2. #2
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help please - how to set target worksheet?

    What's the name of the workbook in which your code resides?
    Where, in that workbook's VBA project, does the code reside (in a module, "ThisWorkbook", or a worksheet's module)?

    What I need to do is set the worksheet name as currently it just imports the data into the activework sheet
    Your code actually imports the data into "Sheets(1)" of the activeworkbook, which may or may not be the active worksheet.
    Is the data being imported into the same workbook as the one where this code resides, and if not, what's the actual name of the target workbook?
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    Board Regular
    Join Date
    Apr 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help please - how to set target worksheet?

    Quote Originally Posted by sykes View Post
    What's the name of the workbook in which your code resides?
    Where, in that workbook's VBA project, does the code reside (in a module, "ThisWorkbook", or a worksheet's module)?


    Your code actually imports the data into "Sheets(1)" of the activeworkbook, which may or may not be the active worksheet.
    Is the data being imported into the same workbook as the one where this code resides, and if not, what's the actual name of the target workbook?
    Sykes, you've just made me understand what's happening here so I've sorted it. Thanks so much!

  4. #4
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help please - how to set target worksheet?

    Great! Much better when you can find the solution yourself, isn't it? Albeit with a tiny nudge in the right direction!
    Thanks for the feedback.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  5. #5
    Board Regular
    Join Date
    Apr 2019
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help please - how to set target worksheet?

    Quote Originally Posted by sykes View Post
    Great! Much better when you can find the solution yourself, isn't it? Albeit with a tiny nudge in the right direction!
    Thanks for the feedback.
    Yes absolutely, thanks a lot mate!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •