copy certain range to different workbook

B

borntobe

Board Regular
Joined
Dec 13, 2013
Messages
66
Hi,<o:p></o:p>
If a cell value is “TOTAL” in the source workbook, then I want to copy certain range to Masterworkbook.<o:p></o:p>
<o:p> </o:p>
Code: 
[FONT=Calibri][SIZE=3][COLOR=#000000]Sub CopyTotaland Paste<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim Rng As Range<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][SIZE=3][COLOR=#000000][FONT=Calibri]Dim VarColNo As Integer<o:p></o:p>[/FONT][/COLOR][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim IRMsheet As Worksheet<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim sourceIRMsheet As Worksheet<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim sourceIRMwb As Workbook<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] Dim zz As Variant<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim Na As String<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim ColNumber As Integer<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim lastIRMr As Integer<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim lastIRMc As Integer<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Dim IRMrng As Range<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Set IRMsheet = ThisWorkbook.Sheets("ABC")<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]zz = Application.GetOpenFilename(FileFilter:="ExcelFiles (*.xlsm), *.xlsm", Title:="Please select a correct projectnumber file")<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]  Workbooks.OpenFilename:=zz, Na = ActiveWorkbook.Name<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] Debug.Print Na<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] Windows(Na).Activate<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] Set sourceIABCwb =ActiveWorkbook<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] <o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Set sourceABCsheet = sourceIRMwb.Sheets("ABC")<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] lastABCc = sourceABCsheet.Cells(1,Columns.Count).End(xlToLeft).Column<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] lastABCr = sourceABCsheet.Cells(Rows.Count,1).End(xlUp).Row<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]For ColNumber = 1 To lastABCc<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][SIZE=3][COLOR=#000000][B][FONT=Calibri]ABCrng =sourceIRsheet.Cells(1, ColNumber)   ‘[/FONT][/B][B][FONT=Wingdings][FONT=Wingdings]ß[/FONT][/FONT][FONT=Calibri]--object required??<o:p></o:p>[/FONT][/B][/COLOR][/SIZE]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]     If ABCrng.Value ="TOTAL QTY" Then<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]        sourceABCsheet.Range(Cells(21,4), Cells(lastIABCr, ColNumber)).Copy ABCsheet.Range(Cells(21, 4), Cells(lastABCr,ColNumber))<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]     <o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Next ColNumber<o:p></o:p>[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]<o:p>[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub<o:p></o:p>[/COLOR][/SIZE][/FONT]
I got an error:<o:p></o:p>
‘object required’<o:p></o:p>
How can I fix this?<o:p></o:p>
Thanks<o:p></o:p>
<o:p> </o:p>
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
D

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,995
Office Version
  1. 2021
  2. 2019
Platform
  1. Windows
Hi,
You have an object variable which should be assigned with the Set keyword.

Try:

Rich (BB code):
Rich (BB code):
Rich (BB code): 
For ColNumber = 1 To lastABCc
Set ABCrng = sourceIRsheet.Cells(1, ColNumber)


'Your code


Set ABCrng = Nothing
Next ColNumber



You should declare the variable as Range.

Dave
 
Last edited:
Upvote 0
B

borntobe

Board Regular
Joined
Dec 13, 2013
Messages
66
I set ABCrng as Range already.
<u1:p></u1:p>Could you expain little more?<o:p></o:p>
<u1:p></u1:p>I still got the same error; object required
Thanks<u1:p></u1:p><o:p></o:p>
 
Last edited:
Upvote 0
D

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,995
Office Version
  1. 2021
  2. 2019
Platform
  1. Windows
borntobe said:
I set ABCrng as Range already.
<u1:p></u1:p>Could you expain little more?<o:p></o:p>
<u1:p></u1:p>I still got the same error; object required
Thanks<u1:p></u1:p><o:p></o:p>
Click to expand...

ABCrng does not appear to have been assigned or delclared in code posted.
Same with sourceIRsheet variable which may explain why you continue to get the error.

Rich (BB code): 
Set ABCrng = sourceIRsheet.Cells(1, ColNumber)

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

rjplante
  • Question
VBA to extract users name from outlook
Replies
1
Views
444
Tupe77
T
A
  • Solved
VbA - copying different colum ranges depending on which condition is met & pasting into different sheet
Replies
5
Views
1K
Fluff
Fluff
W
  • Question
Get data from a separate workbook in a different location WITHOUT opening it
Replies
2
Views
251
Woftae
W
Oberon70
  • Solved
Checking if a reference number exist in a table before copying Data
2
Replies
11
Views
432
Alex Blakenburg
A
H
  • Question
VBA Script to pull certain cells from Marco enable workbook
Replies
0
Views
180
hiteshvarsani
H

Forum statistics

Threads
1,191,499
Messages
5,986,916
Members
440,064
Latest member
Pluong91

Share this page

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
Top