Hey everyone. First, more details on what I'm trying to do. I had previously asked the question on reddit. I have a working sub, that copies the visible range to a new sheet. I'm trying to extend that sub, to be able to take a couple of args, so it can be reused. The code that's currently in use can be seen on pastebin The code that's commented out is the code that works without the attempted use of the variables.
Trying to pass spreadsheet name as an Arg for a copy visible sub
-
workerDrone37 -
August 20, 2016 at 1:58 PM -
Thread is marked as Resolved.
-
-
-
Re: Trying to pass spreadsheet name as an Arg for a copy visible sub
Welcome to Ozgrid workerDrone37. Happy in your job?
Perhaps you'd like to post your code here - might encourage people to help?
-
Re: Trying to pass spreadsheet name as an Arg for a copy visible sub
Hi stephen, the jobs not too bad. Are pastebin links frowned upon here? I can certainly add it here instead
Code
Display MoreSub argTest() Dim destination As Object Set Data = [csv_export_device!a1].CurrentRegion Set destination = Sheets("N2R_Registration") 'Debug.Print destination 'Call copyToNewSheetTest(Data, destination) argTestCall Data, destination End Sub Sub argTestCall(ByVal dataWithHeader As Range, destination As Object) ' data that will be copied Dim destinationSheet As Worksheet Set destinationSheet = destination Dim Data As Range Set Data = dataWithHeader 'Set dataWithHeader = [csv_export_device!a1].CurrentRegion Set dataWithHeader = Data.CurrentRegion With dataWithHeader Set dataOnly = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) End With ' so now you copy data and the header 'dataWithHeader.Copy [N2R_Registration!a1] dataWithHeader.Copy [destinationSheet!a1] End Sub
-
Re: Trying to pass spreadsheet name as an Arg for a copy visible sub
Nothing specifically about pastebin, just better to post code and files here so people don't have to visit elsewhere.
Does this work? Not sure what you're trying to achieve. dataOnly doesn't seem to be used, but I added a declaration which is good practice.
Code
Display MoreSub argTest() Dim destination As Worksheet, Data As Range Set Data = [csv_export_device!a1].CurrentRegion Set destination = Sheets("N2R_Registration") 'Debug.Print destination 'Call copyToNewSheetTest(Data, destination) argTestCall Data, destination End Sub Sub argTestCall(ByVal dataWithHeader As Range, destination As Worksheet) ' data that will be copied Dim destinationSheet As Worksheet Set destinationSheet = destination Dim Data As Range Set Data = dataWithHeader Dim dataOnly As Range 'Set dataWithHeader = [csv_export_device!a1].CurrentRegion Set dataWithHeader = Data.CurrentRegion With dataWithHeader Set dataOnly = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) End With ' so now you copy data and the header 'dataWithHeader.Copy [N2R_Registration!a1] dataWithHeader.Copy destinationSheet.Range("a1") End Sub
-
Re: Trying to pass spreadsheet name as an Arg for a copy visible sub
I THINK the data only might be a hold over from the other usage of the macro, but i'd have to look again.
is the only other change on this line?dataWithHeader.Copy destinationSheet.Range("a1")
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!