Copying cells from one spreadsheet to another with vba

David0079

New Member
Joined
Jul 26, 2007
Messages
44
Hi Gurus,

I've searched the net extensively for an answer to this question but perhaps I'm looking in the wrong places. I need to copy information from specific cells on one spreadsheet to specific cells on another. The problem is that the names of the spreadsheets will not always be consistant. Is there a way in VBA to request the user of a form to select the spreadsheets they want to copy from and too and then somehow reference these in the code?

Any help greatly appreciated?

Dave
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Maybe:

Code:
Sub David0079()
Dim x As String
Dim y As String

x = InputBox("Please Enter the Source Sheet")
y = InputBox("Please Enter the Destination Sheet")

Sheets(x).Range("A1:C2").Copy Sheets(y).Range("A1")

End Sub
 
Upvote 0
Just an added comment to John's response. I think I would specify in the input box message that "Only the sheet name" is to be entered. Otherwise it is subject to input error by some concientious user putting in 'Sheets("Sheet1")' and getting a "Subscript out of range" error when it tries to fing Sheets(x) or Sheets(y).
 
Upvote 0
Hi Guys,

Many thanks for your help with the problem. The names of the workbooks will probably be quite convoluted so I was looking for a solution whereby the user selects the open workbooks rather than typing the whole name in because as JLGWhiz pointed out, there's too much margin for error. So far I have added two comboboxes to a form and populated them like this....

Code:
Private Sub UserForm_Initialize()
Dim wkb As Workbook

With Me.ComboBox1
        For Each wkb In Application.Workbooks
            .AddItem wkb.Name
        Next wkb
    End With
    
        With Me.ComboBox2
        For Each wkb In Application.Workbooks
            .AddItem wkb.Name
        Next wkb
    End With

End Sub

This is working well and I'm seeing a list of all open workbooks in the comboboxes. Nevertheless, I also put a button on there which when clicked will copy specific cells from the first workbook to the second and therein lies my second problem. Is my code below not working because I've referenced the workbooks rather than the sheets within those books?

Code:
Private Sub CommandButton20_Click()
Dim x As ComboBox1.Value
Dim y As ComboBox2.Value

Sheets(X).Range("C58").Copy Sheets(Y).Range("G118")

End Sub

Any ideas guys?

Dave
 
Upvote 0
The Dim statement is to declare data types, not values. Try this revised version.

Code:
Private Sub CommandButton20_Click()
Dim X As String
Dim Y As String
X = ComboBox1.Value
Y = ComboBox2.Value
Sheets(X).Range("C58").Copy Sheets(Y).Range("G118")
End Sub
Code:
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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