CODEWITHSUNDEEP
excelvba
user3470110
user3470110

Reputation: 13

VBA-Excel 2010 Macro Error "memeber or data method not found"

I know this is a super generic error but I am new to VBA / Macros and cant get past this.

I have an and excel workbook that has data I need to copy to another excel workbook. The excel workbook that the data is copied to is on a network share and will be written to frequently.

here is my macro code:

Sub export()
Dim exportFile As String
Dim importSheet As String
Dim rowData As String

exportFile = "\\<server>\spd\_Spec_ParaData\data_import.xlsx"
importSheet = "OutPutValues"
importRange = "A2:ZZ2"

' Get the row from the workbook that we are running in
rowData = Workbooks().Worksheets(importSheet).Range(importRange)

' Not sure if this will work, or always overwrite the last row. May need to be .Row+1
newRow = Workbooks(exportFile).Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row

exportRange = "A" & (newRow + 1) & ":ZZ" & (newRow + 1)

' Assuming Workbooks() gets the current workbook.
Workbooks(exportFile).Sheets(exportSheet).Range(exportRange) = Workbooks().Sheets(importSheet).Range(importRange)

End Sub

My error is poping up on the rowData=Workbooks(exportFile).Worksheets

Can someone help me figure out what I am doing wrong?

Thank you, Jennifer

Upvotes: 0

Views: 413

Answers (1)

Matteo NNZ
Matteo NNZ

Reputation: 12665

Try your code with the following modifications, I'm just opening the workbook and referencing the worksheet (I guess the problem is that). I'm closing the workbook straight after.

Sub export()
Dim exportFile As String
Dim importSheet As String
Dim rowData As String
Dim wb As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

exportFile = "\\<server>\spd\_Spec_ParaData\data_import.xlsx"
importSheet = "OutPutValues"
importRange = "A2:ZZ2"

'Open your workbook and point to your spreadsheet    
Set wb = Workbooks.Open(exportFile)
Set ws1 = wb.Sheets(importSheet)    

' Get the row from the workbook that we are running in
rowData = wb.ws.Range(importRange)

' Not sure if this will work, or always overwrite the last row. May need to be .Row+1
newRow = wb.Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row

exportRange = "A" & (newRow + 1) & ":ZZ" & (newRow + 1)

'NOTE: consider definying the variable "exportSheet", I will do it just for example purpose
Dim exportSheet As String: exportSheet = "InputValues"
Set ws2 = wb.Sheets(exportSheet)

' Assuming Workbooks() gets the current workbook.
wb.ws2.Range(exportRange) = wb.ws1.Range(importRange)
wb.Close

End Sub

Upvotes: 0

Related Questions