7 Replies - 376 Views - Last Post: 18 January 2019 - 09:54 AM Rate Topic: -----

#1 kyle01   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 208
  • Joined: 26-November 14

MS Excel Macro Simplification Help

Posted 18 January 2019 - 06:38 AM

I have created this code that basically looks in one workbook, finds the range that i specify then copies it to the destination workbooks column range. I have about 12 more columns to do but before I do that, is there a nice way of simplifying the code to make it less lines or more elegant?

Sub Button1_Click()
'Field Name
Windows("childsheet.xlsm").Activate
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("parentsheet.xlsm").Activate
Range("A3").Select
ActiveSheet.Paste

'API Name
Windows("childsheet.xlsm").Activate
Range("B3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("parentsheet.xlsm").Activate
Range("B3").Select
ActiveSheet.Paste

'Type
Windows("childsheet.xlsm").Activate
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("parentsheet.xlsm").Activate
Range("C3").Select
ActiveSheet.Paste

'Length
Windows("childsheet.xlsm").Activate
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("parentsheet.xlsm").Activate
Range("D3").Select
ActiveSheet.Paste

'Required
Windows("childsheet.xlsm").Activate
Range("E3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("parentsheet.xlsm").Activate
Range("E3").Select
ActiveSheet.Paste

'Read Only?
Windows("childsheet.xlsm").Activate
Range("F3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("parentsheet.xlsm").Activate
Range("F3").Select
ActiveSheet.Paste
End Sub



Is This A Good Question/Topic? 0
  • +

Replies To: MS Excel Macro Simplification Help

#2 kyle01   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 208
  • Joined: 26-November 14

Re: MS Excel Macro Simplification Help

Posted 18 January 2019 - 07:25 AM

also how do i copy the column even if it has blanks? currently it stops if it encounters a blank cell
Was This Post Helpful? 0
  • +
  • -

#3 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15160
  • View blog
  • Posts: 60,694
  • Joined: 12-June 08

Re: MS Excel Macro Simplification Help

Posted 18 January 2019 - 08:34 AM

Looks like you could use a loop and change the letter to combine with the number to get your range.
Was This Post Helpful? 0
  • +
  • -

#4 kyle01   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 208
  • Joined: 26-November 14

Re: MS Excel Macro Simplification Help

Posted 18 January 2019 - 08:36 AM

would that also get rid of the current problem, where the copy function stops once it hits a empty cell?
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6805
  • View blog
  • Posts: 28,134
  • Joined: 12-December 12

Re: MS Excel Macro Simplification Help

Posted 18 January 2019 - 08:38 AM

Presumably this was forged from recorded macros. It can be improved greatly with zero use of Activate and Select. Even Paste can often be substituted by directly assigning values and/or using arrays.

Once it is fully working and tested, turn off screen updating.
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15160
  • View blog
  • Posts: 60,694
  • Joined: 12-June 08

Re: MS Excel Macro Simplification Help

Posted 18 January 2019 - 08:39 AM

Logically I would think no.. since that is only taking care of A, B, C, D, etc incrementing. That empty cell may be a function setting in your repeated code you are missing. Though it seems odd it would 'stop'.
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6805
  • View blog
  • Posts: 28,134
  • Joined: 12-December 12

Re: MS Excel Macro Simplification Help

Posted 18 January 2019 - 08:40 AM

View Postkyle01, on 18 January 2019 - 03:36 PM, said:

would that also get rid of the current problem, where the copy function stops once it hits a empty cell?

You could start further down (or at the bottom of the sheet) and come up (End(xlUp)), although there are slightly neater ways to do this, perhaps using UsedRange.
Was This Post Helpful? 0
  • +
  • -

#8 kyle01   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 208
  • Joined: 26-November 14

Re: MS Excel Macro Simplification Help

Posted 18 January 2019 - 09:54 AM

Sub Button1_Click()
With Workbooks("childsheet.xlsm").ActiveSheet
    .Range("A3", "A" & Application.Max(.Cells(.Rows.Count, "A").End(xlUp).Row, 3)).Copy Destination:=Workbooks("parentsheet.xlsm").Worksheets("Account").Range("A3")
    .Range("B3", .Cells(.Rows.Count, "B").End(xlDown)).Copy Destination:=Workbooks("parentsheet.xlsm").Worksheets("Account").Range("B3")
End With
End Sub




> this code works but only if in row 1 i have a merged cell that goes from A1:F1
> the error appears: "Run Time error: '1004': We can't do that to a merged cell.'
> if i remove the merged cell, it works. but I need the merged cell to be there, so is there anyway of skipping that row and starting from row 3 instead? .. also, can i specify what rows i want to copy, as now, it's just copying the whole of column A and column B, but i want e.g. copy from A3 to A12 only.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1