Can't get Workbook/sheet reference to work in my function

most

Board Regular
Joined
Feb 22, 2011
Messages
107
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
Going crazy on this one, what i'm doing wrong?
It's an "Object variable or With block variable not set" on "Set FromWbs" in the function.
/Marcus


Code:
Public FromWorkbook As Workbook
Public ToWorkbook As Workbook
Public FromStartRow As Integer
Public ToStartRow As Integer
Sub CopyColumn2Column()
' Use of copy column function
'
  Dim FromWorkbook As Workbook, ToWorkbook As Workbook


 'Global parameters
  Set FromWorkbook = Workbooks("Book1")
  Set ToWorkbook = Workbooks("Book2")
  FromStartRow = 2
  ToStartRow = 3


'CopyC2C(Name, FromSheet, FromColumn, ToSheet, ToColumn)
  Call CopyC2C("Run 01 - Test", "Sheet2", 1, "Sheet1", 2)


End Sub
Function CopyC2C(Name As String, ByRef FromSheet As String, FromColumn As Integer, ByRef ToSheet As String, ToColumn As Integer)
' Function, Copy column to column between two diffrent sheets
'  
  Set FromWbs = FromWorkbook.Sheets(FromSheet)
  Set ToWbs = ToWorkbook.Sheets(ToSheet)
    LastRow = FromWbs.Cells(Rows.count, FromColumn).End(xlUp).Row
    Debug.Print "CopyC2C - " & Name & " | " & FromSheet & ", " & FromColumn & " | " & ToSheet & ", " & ToColumn & " | " & LastRow
    FromWbs.Range(Cells(FromStartRow, FromColumn), Cells(LastRow, FromColumn)).Copy ToWbs.Cells(ToStartRow, ToColumn)
End Function
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
What is FromSheet? Is that the actual name of a sheet? If so, it needs to be like "FromSheet"
Otherwise it should be something like
Code:
Set FromSheet = FromWorkbook.Sheets("Sheet1")    'or whatever sheet you want to refer to
 
Upvote 0
FromSheet is a input variable (string) in the function.
I already have "Set FromWbs = FromWorkbook.Sheets(FromSheet)" and it's on that row Excel complains.
 
Upvote 0
Remove this line from the code
Code:
Dim FromWorkbook As Workbook, ToWorkbook As Workbook
 
Upvote 0
Remove this line from the code
Code:
Dim FromWorkbook As Workbook, ToWorkbook As Workbook

I'm usually very lazy when it comes to declaring, and now when I do, it back fires. Hahaha... Thanks!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

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